MySQL: Uønsket [null] ved import av csv.

[Cerwell]

Hei

Kan dere hjelpe mwg med å løse dette?

 

Ved import av csv til MySQL får jeg en uønsket [null] mellom hvert tegn:

String inn: 12

Resultat: [null]1[null]2[null]

 

Jeg bruker en prosedyre som denne:

 

LOAD DATA LOCAL INFILE 'uniq.csv'

INTO TABLE tblUniq

FIELDS TERMINATED BY ';'

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 LINES

(uniqName, uniqCity, uniqComments)

 

[saks fra:http://stackoverflow.com/questions/11077801/import-csv-to-mysql-table]

[Crowly]

Hvordan ser linjene i csv fila ut? Og hvordan er tabell strukturen, kan noen av feltene være null?

 

Du kan bruke sql funksjoner i SET delen, som IFNULL() og NULLIF() til å behandle input før det blir lest inn i basen, da må feltene først inn i en variabel (angis med @). Variabel navnet må ikke være det samme som felt navnet.

 

 

LOAD DATA INFILE 'uniq.csv'
INTO TABLE tblUniq
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@name, @uniqCity, @comments)
SET
    uniqName = IFNULL(@name,''),
    uniqCity = IFNULL( @uniqCity,''),
    uniqComments = IFNULL(@comments,'')

 

En miks med variabler, hvor uniqComments leses rett inn i tabellen, mens uniqName og uniqCity kjøres igjennom IFNULL()

 

 

LOAD DATA INFILE 'uniq.csv'
INTO TABLE tblUniq
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@name, @uniqCity,  uniqComments)
SET
    uniqName = IFNULL(@name,''),
    uniqCity = IFNULL( @uniqCity,'')

[Cerwell]

Hei og takk for svar.

Problemet her er ikke at et 'felt' i csv er null, men at det dyttes inn en null mellom hvert tegn fra feltet.

Fikk dermed ikke Ifnull til å gjøre noen forskjell.

Jeg mistenker at det er noe feil med CharSet men har ikke funnet løsningen der.

[Crowly]

Får du NULL (som i ingenting), eller bare ett blankt tegn/space i mellom?

 

Hvis ett space, så vil trim() gjøre suses:

LOAD DATA INFILE 'uniq.csv'
INTO TABLE tblUniq
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@name, @uniqCity, @comments)
SET
    uniqName = TRIM(@name),
    uniqCity = TRIM(@uniqCity),
    uniqComments = TRIM(@comments)

Hvis det er ett tegnsett problem, har du sjekket tegnesettet på mysql tilkoblingen, tabell feltene og input data?

 

Har du en skjermdump e.l., har litt problemer med å forstå hvordan resultatet i tabellen blir.

[Cerwell]

Her vises CSV fila via notepad og data importert til tabell.

Endret 17. mars 2015 av Cerwell

[Crowly]

Den var ny for meg. Er du sikker på at NUL ligger i csv fila? Notepad er en litt for primitiv editor og viser ikke slike tegn. Åpne fila i f.eks. notepad++ og slå på vis alle tegn (view -> show symbol -> all characters)

 

Eller du kan prøve å kjøre en replace() med char(0), det skal erstatte alle forekomster av acsii verdi 0 (NULL) med en tom streng. Litt usikker på om 0 er rett verdi til char(). Mulig du kan erstatte char(0) med unhex(0x00) for å søke etter hex verdien i stedet for.

 

LOAD DATA INFILE 'uniq.csv'
INTO TABLE tblUniq
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@name, @uniqCity, @comments)
SET
    uniqName = REPLACE(@name,CHAR(0),''),
    uniqCity = REPLACE(@uniqCity,CHAR(0),''),
    uniqComments = REPLACE(@comments,CHAR(0),'')

Du kan jo teste det i en select først og se om du før ønsket resultat

SELECT REPLACE(felt,UNHEX(0x00),'') test
FROM tabell

Endret 17. mars 2015 av Crowly

[Cerwell]

Hei og takk nok en gang.

 

Har nå sjekket med Notepad++, og de eneste skjulte tegnene er CR/LF på slutten av hver linje.

Resten er tight og fint.

[Crowly]

Da må jeg melde pass

Siste (dårlige?) løsning er å kjøre en update på tabellen etter import for å strippe vekk uønskede tegn.

[Yawa]

Dette var rart. Om du ønsker, kan du laste opp csv.csv-filen din her slik at vi se på den. Med mindre den ikke er meget sensitiv...

Det kan virke som det er noe som skjer ved import - altså at det er noen usynlige karakterer som ikke synes.

Det kan ha med tegnsett o.l. å gjøre. Har du sjekket at .csv-filen har samme tegnsett som databasetabellen? og at når du importerer at du velger å benytte riktig tegnsett?

 

Har du forsøkt å laste inn med annet verktøy - som eks. direkte i phpMyAdmin?

[Djn]

Fila er sannsynligvis i UTF-16 eller UCS2. Ikke et format jeg ser veldig ofte i det fri, men windows og OS X bruker dem internt. For å gi en minimal introduksjon til tekst-enkoding:

 

ANSI/Latin1/ISO-8859-x bruker en byte per tegn, og kan dermed maksimalt ha 256 mulige tegn.

UTF-8 bruker en byte per tegn om mulig, men kan bruke flere om nødvendig.

UCS2 bruker alltid to bytes per tegn, og kan maksimalt ha 65536 forskjellige tegn.

UTF-16 bruker to byte per tegn om mulig, men kan bruke flere om nødvendig.

 

Dette ser ut som UCS2 eller UTF-16 - for latinske bokstaver blir den første byten i hvert tegn 0, og den andre det samme som i enkodingene med én byte. Notepad støtter alle disse formatene og vil tolke de riktig, derfor ser det fint ut der. MySQL, derimot, har en kommentar om at "It is not possible to load data files that use the ucs2, utf16, utf16le, or utf32 character set". Det du ser i MySQL er at den tolker hver byte som et separat tegn i stedet for å lese to og to - og da blir naturlig nok annenhvert tegn null.

 

Altså:

I Latin1 og UTF-8 er A 41.

I UCS2 og UTF-16 er A 0041.

MySQL leser 0041 som 00, 41 (null, A) i stedet for 0041 (A).

 

 

Det er heldigvis ganske greit å løse - du kan Save As i notepad og velge en annen encoding nederst i vinduet. UTF-8 er vanligvis det beste.

Endret 17. mars 2015 av Djn

[Cerwell]

Takk Djn!

Dette er en forklaring jeg biter på.

Jeg forsøkte å lagre i utf-8. Det virker.

Jeg hadde håpet at det var en enklere løsning.

Jeg får be leverandøren endre koding på fila.

[Djn]

Hva skal du gjøre med den i lengden? Jeg antar du ikke har tenkt å importere den "for hånd" i windows veldig ofte?

Hvis du skal skrive noe for å gjøre dette på en linux-server er det lett nok å konvertere den automatisk med iconv et sted i prosessen, og jeg antar windows-folket kan komme opp med noe tilsvarende. (Du kan vel til og med gjøre det i PHP.)

Endret 17. mars 2015 av Djn

[Cerwell]

Dette er salgstall som jeg må laste ned manuelt fra web hver gang jeg ønsker oppdaterte tall. Typisk hver måned.

Servere er win men målet er frontend med php.

Jeg er ny på SQL og php så det blir steg for steg.

Så snart det virker med manuell import så er automatisering aktuelt.

 

Jeg kan kode om fila via vb.net, men jeg håper jeg slipper å gå den omveien.

 

Hvordan jeg best løser dette er dermed fortsatt i det blå.

[Djn]

Meh, windows-servere.

 

Vel, du kan kanskje få automatisert nedlastingen. PHP kan bruke curl til å laste ned filer (og opptil flere andre metoder ... men jeg tror curl er den mest fleksible), og det er mulig å rigge det til å logge inn først og slikt, om nødvendig. Merk dog at "mulig" og "lett" ikke er det samme.

 

Du kan også laste ned for hånd og lage en side hvor du så laster den opp manuelt, resten av prosessen blir uansett det samme.

 

PHP har også, i typisk PHP-stil, minst tre forskjellige måter å konvertere mellom enkodinger. Du kan lese hele filen til en variabel og konvertere den (med iconv eller mb_convert_encoding), eller så kan du åpne den og legge til et filter som konverterer mens du leser med stream_encoding ... men jeg finner ingen brukelig dokumentasjon på hvordan det fungerer.

 

Uansett kan du velge om du vil skrive den konverterte utgaven til en fil og LOADe den, eller håndtere en linje om gangen i PHP og gjøre INSERT/UPDATE fra PHP-siden.

Endret 17. mars 2015 av Djn

[Crowly]

Hvis man skal kjøre en insert fra php, så støtter mysql i alle fall opptil 50 rader samtidig i en insert

INSERT INTO tabell (felt)
VALUES (1),
(2),
(3),
....
(50)

Det er langt mer effektivt enn å lese inn en og en rad. Basert på min erfaring er LOAD DATE INFILE den raskeste (i ren innlesning), men da må man konvertere tegnsettet først og det er uansett total tiden som teller. Og så spørs det hvor stor rolle det spiller her