ezz

Jeg har store problemer med TAB-separerede filer fra Excel til
indlæsning i en SQL-database. Indlæsningen stopper når den møder en
tekststreng med apostrof.

StrConv("Tekststreng", 128) skulle kunne konvertere til det tegnsæt der
er default for systemet (Mac hos mig).

Problemet er, at jeg har 26.000 rækker og 16 af kolonnerne indeholder tekst.

Jeg skal sådan set bare have et output til en TAB-separeret fil uden
Unicode, men om dette er løsningen eller ej, kan jeg ikke gennemskue.

Problemet er velkendt, kan jeg se med Google, men jeg er ikke klog nok
til at gennemskue nogle af de workarounds der nævnes.

On Wed, 22 Feb 2012 12:36:04 +0100, Kurt Hansen <kurt>
wrote:

>Jeg har store problemer med TAB-separerede filer fra Excel til
>indlæsning i en SQL-database. Indlæsningen stopper når den møder en
>tekststreng med apostrof.
Hvad har du deklaretet det felt til som data skal ind i ? Måske det
ikke understøtter det du hælder ind ?
Hvilken SQL benytter du ? Hvad er feltet deklareret som ?
Hvordan fylder du data på din SQL ? er det et export værktøj, eller
har du selv kodet ?

Måske apostrof er det samme som "end of text" for den datatype som du
benytter i databasen, og fejlen kan være at "end of text" (din
apostrof) gør at efterfølgende tekst bliver opfattet som ulovlig da
det sådanset laver ged i systemet.

>StrConv("Tekststreng", 128) skulle kunne konvertere til det tegnsæt der
>er default for systemet (Mac hos mig).
>
>Problemet er, at jeg har 26.000 rækker og 16 af kolonnerne indeholder tekst.
>
>Jeg skal sådan set bare have et output til en TAB-separeret fil uden
>Unicode, men om dette er løsningen eller ej, kan jeg ikke gennemskue.
>
>Problemet er velkendt, kan jeg se med Google, men jeg er ikke klog nok
>til at gennemskue nogle af de workarounds der nævnes.
Først skal du lige have overblik over hvilke tegnsæt (sprogvarianter)
du benytter i dine tekster, er det mere end en/et kan du ikke uden tab
gå fra unicode til ikke unicode.

Almindelig tekst er, en byte til et bogstav/tegn, men unicode er flere
bytes til et bogstav/tegn.

Derfor kan unicode strenge indeholde tegn fra flere sprogvarianter
dansk/russisk/kinesisk på samme tid og det vil blive præsenteret
korrekt, men det skal understøttes både i SQL databasen og af det
værktøj der lægger data ind og trækker dem ud, og af den applikation
der skal præsenterer/behandle data.

Skal det gå godt skal du kører med samme struktur hele vejen fra dit
excel arks celle til din SQL database, altså unicode i din excel
celle, unicode i din VBA applikation (eller hvad du nu benytter til at
overfører data), unicode i den applikation der skal være frontend mod
brugeren.

Er det noget der skal ud og kører i virkeligheden skal du også
grundigt undersøge om alle tænkelige (og måske for dig utænkelige)
sprogvarianter af OS og applikationer der kan tænkes at være i spil.
Tag ikke for givet at det vil virke på et engelsk/russisk/kinesisk OS
eller tilsvarende variant af excell eller frontend applikation.


/Hans

Den 22/02/12 13.13, Hans Kjaergaard skrev:
> On Wed, 22 Feb 2012 12:36:04 +0100, Kurt Hansen<kurt>
> wrote:
>
>> Jeg har store problemer med TAB-separerede filer fra Excel til
>> indlæsning i en SQL-database. Indlæsningen stopper når den møder en
>> tekststreng med apostrof.

> Hvad har du deklaretet det felt til som data skal ind i ? Måske det
> ikke understøtter det du hælder ind ?
> Hvilken SQL benytter du ? Hvad er feltet deklareret som ?
> Hvordan fylder du data på din SQL ? er det et export værktøj, eller
> har du selv kodet ?

Tak for viljen til at hjælpe. Desværre ligger dine spørgsmål flere
spadestik over mit niveau. Mil tilbagesvar bliver nok desværre lang, for
jeg aner ikke hvad der er relevant, men jeg forsøger.

Kort problembeskrivelse.

Jeg kører Mac og har hidtil brugt Excel til at manipulere data til to
webshops der ligge på samme webhotel. Shoppen er en dansk variant af
osCommerce, som jeg ikke selv har lavet og derfor ingen indflydelse har
på (det hedder Unique Free).

Når jeg gemmer som TAB-separeret fil indlæser jeg den derefter i
TextWrangler og gemmer derfra med Windows CR og Windows Western Latin 1.
Alt andet viser ikke specialkarakterer korrekt.

I shoppens kontrolpanel importerer jeg gennem et implementeret modul der
hedder EasyPopulate og det har virket upåklageligt indtil nu.

Nu har jeg, på en ny database, installeret en frisk kopi af
shopsoftwaren og så går det galt. Importen går i stå ved den første
apostrof. Jeg husker, og kan stadig se mit indlæg i Unique's
supportforum, at jeg havde samme problem helt tilbage i begyndelsen, men
spørgsmålet står stadig åbent uden svar.

Jeg har altså selv løst problemet i sin tid, men har ingen som helst
erindring om hvordan.

To to gamle shops fungerer stadig og hvis jeg tager en af de
TAB-separerede filer, som er uploadet til en af de to shops, importerer
det nok så nyedligt i den nye database. Jeg må så gå ud fra, at det er
de nye filer jeg laver den er gal med.

Og her kommer det et nyt aspekt ind. Jeg er ved at rykke fra Excel over
på Filemaker Pro 11 Advanced.

Filemaker kan gemme som TAB, men af uransagelige og uforståelige grunde,
kommer feltnavnene ikke med i den første linje. Og nej ... det er ikke
fejlbetjening; det er et faktum der kan bekræftes.

Den kan også gemme som en kommasepareret fil (Merge til Outlook vistnok)
og der kommer kolonneoverskrifterne med. Desværre kan Easy Populate kun
håndtere TAB, så det duer ikke. Hvis jeg prøver at importere merge filen
(omdøbt til *.csv) i phpMyAdmin standser importen ved den første apostrof.

Jeg kan ikke mindes (men jeg kan have fortrængt det) at jeg har ændret
på nogle indstillinger i phpMyAdmin. Det har jeg ingen forstand på, så
det ville være halsløs gerning.

Her er lidt info:

MySQL
Server: Localhost via UNIX socket
Serverversion: 5.1.48
Protokolversion: 10
MySQL Tegnsæt: UTF-8 Unicode (utf8)
Webserver
Apache/1.3.42 (Unix) PHP/5.3.2
MySQL klientversion: 5.1.48
PHP udvidelse: mysqli Dokumentation
phpMyAdmin
Versionsinformation: 3.4.7

Jeg klipper også lige et indlæg jeg havde i Unique's forum for ganske
nyligt, men udvikleren erkender blankt, at han ikke er i stand til at svare.

- - -
Det ville være rart med lidt grundlæggende og jeg vil gerne bede Kennith
om en kommentar til følgende punkter:

Når jeg logger ind i phpMyAdmin står der på forsiden under General
Settings, at MySQL forbindelses-sammenkøring er utf8_general_ci.
Under MySQL står der: MySQL Tegnsæt: UTF-8 Unicode (utf8) .
Under Settings > Import > Standarder for import og tilsvarende
under Eksport er feltet Filens tegnsæt tom. Der kan vælges fra en dropdown.
Hvis jeg vælger Brugerdefineret i stedet for Hurtig, står der under
Output, at standard for filen er utf-8.


Og så kommer vi til det interessante, men også uforståelige for hvide
mennesker:

Under fanen "Variabler" er der en masse indstillinger for tegnsæt. Nogle
står til utf-8, andre til latin1 og for Collation anvendes henholdsvist
utf8_general_ci og latin1_swedish_ci

Hvorfor denne babyloniske forvirring?

Hvad er Collation?

Burde UTF-8 ikke bare være standard overalt?
- - -

For god ordens skyld gengiver jeg her en lille test jeg lige har udført.
Jeg har lavet en TAB-separeret fil med kun en post, nemlig den første
der giver problemer med apostrof.

Den tilter også i de gamle databaser!!!

Jeg må, med de fattige evner jeg har, konkludere, at det er filen og
ikke databaseopsætningen der er problemet.

Vejen fra Filemaker er knudret. I dette tilfælde har jeg åbnet
Filemaker-databasen i Excel, gemt som TAB og efterfølgende konverteret
til Windows CR og Windows Western Latin 1 i Textwrangler, hvilket er den
opskrift jeg plejer at benytte uden problemer.

Jeg ved ikke om det hjælper noget og og det gengives korrekt i en news
client, men kommer først indholdet af filen og dernæst fejlmeddelelsen:

v_products_model v_products_name_1 v_products_description_1
v_products_page_title_1 v_products_meta_keywords_1
v_products_meta_description_1 v_products_short_description_1
v_products_name_2 v_products_description_2 v_products_page_title_2
v_products_meta_keywords_2 v_products_meta_description_2
v_products_short_description_2 v_products_image v_products_price_per
v_products_sort_order v_products_manufacturer_model v_products_ean
v_products_price v_products_quantity v_products_weight v_date_avail
v_categories_name_1_1 Komplet v_categories_name_1_1
v_categories_name_2_1 v_categories_name_3_1 v_manufacturers_name
v_tax_class_title v_status EOREOR
AAA 001 <b>That's Entertainment</b> CDTER 1117 "<b>Coward Noel</b>: Noël
And Gertie / <i>Fiander Lewis / Hodge Patricia</i>
Label: <b>That's Entertainment</b> - Best. nr.: CDTER 1117
EAN-nummer: 5015062111729" Danacord B2B: Coward Noel - Noël And Gertie
Coward Noel Fiander Lewis / Hodge Patriciaklassisk, cd, danacord,
pladecentret, pladecenteret, danacord Coward Noel: Noël And Gertie -
Fiander Lewis / Hodge Patricia <b>Coward Noel</b> <i>Noël And Gertie -
Fiander Lewis / Hodge Patricia <b>That's Entertainment</b> CDTER 1117
"<b>Coward Noel</b>: Noël And Gertie / <i>Fiander Lewis / Hodge Patricia</i>
Label: <b>That's Entertainment</b> - Best. nr.: CDTER 1117
EAN-nummer: 5015062111729" Danacord B2B: Coward Noel - Noël And Gertie
Coward Noel Fiander Lewis / Hodge Patriciaklassisk, cd, danacord,
pladecentret, pladecenteret, danacord Coward Noel: Noël And Gertie -
Fiander Lewis / Hodge Patricia <b>Coward Noel</b> <i>Noël And Gertie -
Fiander Lewis / Hodge Patricia produkter/That's
Entertainment/5015062111729.jpg 1 5015062111729 129 100 0,083333333
Komplet katalog Klassisk That's Entertainment Moms Active EOREOR

- - -

Easy Populate 2.76g-MS2 - Default Language : danish(1)

File uploaded.
Temporary filename: /var/www/tmp/php3JT8Ka
User filename: AAA 001.txt
Size: 1787
| AAA 001 | That's Ent | Coward Noe | Danacord B | Coward Noe | Coward
Noe | Coward Noe | That's Ent | Coward Noe | Danacord B | Coward Noe |
Coward Noe | Coward Noe | produkter/ | 1 | | | 5015062111 | 129 | 100 |
0,08333333 | | Komplet ka | | Klassisk | | That's Ent | Moms | Active
!New Product!
1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 's Entertainment/5015062111729.jpg', '','','1',''' at line 15

INSERT INTO products ( products_image,
products_inner_box,products_outer_box,products_pri ce_per,products_sort_order,products_manufacturer_m odel,products_ean,products_subimage1,products_subi mage2,products_subimage3,products_subimage4,produc ts_subimage5,products_subimage6,
products_model, products_price, products_status, products_last_modified,
products_date_added, products_date_available, products_tax_class_id,
products_weight, products_quantity, manufacturers_id ) VALUES (
'produkter/That's Entertainment/5015062111729.jpg',
'','','1','','','5015062111729','','','','','','', 'AAA 001', '129',
'1', '2012-02-22 15:00:20', '2012-02-22 15:00:20', '0000-00-00
00:00:00', '1', '0,083333333', '100', 173)

[TEP STOP]

On Wed, 22 Feb 2012 15:04:06 +0100, Kurt Hansen <kurt>
wrote:

Her går det galt
*****************
>1064 - You have an error in your SQL syntax; check the manual that
>corresponds to your MySQL server version for the right syntax to use
>near 's Entertainment/5015062111729.jpg', '','','1',''' at line 15
*****************
Og det er apostroffen i That's Entertainment der bliver tolket som
"end of field" og derfor er det faktisk teksten "s Entertainment....."
der skaber dit problem.
I nogle script/programmerings sprog skriver man 2eller 3 * apostrof i
træk når man har behov for at fortælle programmet at denne ene
apostrof skal ikke bruges som field delimiter (afslutning af streng).

Det er rigmeligt tydeligt at dit system benytter apostrof til at
omkredse strenge og derfor går jo så også galt når du har en apostrof
i selve strengen.
Prøv evt. manuelt at tilføje hhv 1 og 2 ekstra apostroffer i filen og
se om systemet så vil æde den.
Hvis det virker så skal du inden eksport af dine data have lavet dine
tekst apostroffer om fra en til 2 eller 3.

Det kan også være at der skal benyttes en helt anden form for notation
omkring dine tekst strenge, men det skal du se i dokumentationen
efter.

Ps så kan jeg ikke udmildbart se at der bliver brugt unicode i det du
eksporterer, så man tager nok en given codepage for givet.
Hvis det var unicode som rå data ville det se noget anderledes ud od
det der kunne læses som klar tekst ville stå som ord med mellemrum
imellem alle bogstaver, eks. "T e s t . t x t".


/Hans

Den 22/02/12 17.42, Hans Kjaergaard skrev:
> On Wed, 22 Feb 2012 15:04:06 +0100, Kurt Hansen<kurt>
> wrote:
>
> Her går det galt
> *****************
>> 1064 - You have an error in your SQL syntax; check the manual that
>> corresponds to your MySQL server version for the right syntax to use
>> near 's Entertainment/5015062111729.jpg', '','','1',''' at line 15
> *****************
> Og det er apostroffen i That's Entertainment der bliver tolket som
> "end of field" og derfor er det faktisk teksten "s Entertainment....."
> der skaber dit problem.

Det tror jeg ikke. Jeg anerkender din tekniske forklaring, men dette må
skyldes noget andet "That's Entertainment" findes i linje 9.407, men den
første apostrof kommer allerede i linje 8 og da der er over 9.000 i hele
arket, er der mange før det. MEN ... det er den første forekomst i DEN
kolonne.

Felttypen hvor det går galt er varchar(255), hvor de andre teksfelter i
posten står som "text". Som test ville jeg prøve at ændre varchar(255) i
dropdownboxen ved siden af, men blandt mange felttyper, kunne jeg ikke
finde nogen der synes at passe. TEXT er der slet ikke. Ud over 'char'
var der kun 'ascii' der smagte lidt at fugl, men det gjorde ingen forskel.

Den med at skrive to eller tre apostroffer er for så vidt god nok. Hvis
jeg skriver 2 kan filen importeres, men ved 3 får jeg den velkendte fejl.

Når jeg så erstatter ' med '' op uploader, sker det fejlfrit. Desværre
bliver de to apostroffer også vist på brugerfladen i browseren og det
ser jo ikke kønt ud.

Desuden orker jeg ikke at skulle bruge søg/erstat hver gang jeg vil
opdatere min database. Kan det være rigtigt at jeg skal "nøjes" med
denne løsning?

On Wed, 22 Feb 2012 19:52:18 +0100, Kurt Hansen <kurt>
wrote:

>Den 22/02/12 17.42, Hans Kjaergaard skrev:
>
>Det tror jeg ikke.
Ok, men jeg vil alligevel fastholde min påstand indtil andet er
bevist.

>Jeg anerkender din tekniske forklaring, men dette må
>skyldes noget andet "That's Entertainment" findes i linje 9.407, men den
>første apostrof kommer allerede i linje 8 og da der er over 9.000 i hele
>arket, er der mange før det. MEN ... det er den første forekomst i DEN
>kolonne.
For at komme det nærmere skal jeg kunne se dine data i sin helhed og
de værktøjer du benytter, og det lader sig ikke gøre i dette fora, så
den vej kommer vi det ikke nærmere.

>Felttypen hvor det går galt er varchar(255), hvor de andre teksfelter i
>posten står som "text". Som test ville jeg prøve at ændre varchar(255) i
>dropdownboxen ved siden af, men blandt mange felttyper, kunne jeg ikke
>finde nogen der synes at passe. TEXT er der slet ikke. Ud over 'char'
>var der kun 'ascii' der smagte lidt at fugl, men det gjorde ingen forskel.
Se her har du fat i noget af det rigtige, du skal vælge den rigtige
felttype, så langt så godt, men så simpelt er det så desværre ikke.
Du skal kigge dybt i dokumentationen for de/det program du bruger for
at finde frem til den/de felttyper der passer til de strenge du kan
komme ud for at skulle exporterer/importerer, men legen stopper ikke
der for felttyperne skal jo også passe der hvor data skal hen, det er
ikke nok at du får dem ud, de skal jo også videre.
"varchar" er ikke bare "varchar", hvad den kan indeholde afhænger af
hvilken applikation der benytter det og for at det ikke skal være løgn
også af versionen (MySQL behandler det forskelligt i versionerne før
og efter 5.0.3 bare for at nævne et eksemple).
Så det er hårdt benarbejde med at studerer dokumentation for alle de
applikationer som dine data skal igemmen.
Selvfølelig kan du bare prøve dig frem ad træskovejen, men gør det
systematisk og skriv ned hvad du har forsøgt og hvad resultatet var.

>Den med at skrive to eller tre apostroffer er for så vidt god nok. Hvis
>jeg skriver 2 kan filen importeres, men ved 3 får jeg den velkendte fejl.
Det kan også være at det er de apostroffer der omkrenser din streng du
skal doble eller trible, prøv.

>Når jeg så erstatter ' med '' op uploader, sker det fejlfrit. Desværre
>bliver de to apostroffer også vist på brugerfladen i browseren og det
>ser jo ikke kønt ud.
Nej, men nogen gange må man vælge imellem flere onder, og ved valg
mellem kosmetiske fejl og funktions fejl, vælger jeg de kosmetiske.

>Desuden orker jeg ikke at skulle bruge søg/erstat hver gang jeg vil
>opdatere min database. Kan det være rigtigt at jeg skal "nøjes" med
>denne løsning?
VBA, kald det macro, men det kan være en brugbar vej til en
automatiseret løsning, den vej burde du også kunne ekspoterer dine
data fra excel.


/Hans

On Wed, 22 Feb 2012 14:38:58 +0100, Kurt Hansen <kurt>
wrote:

Lige for at klarlægge flowet:
>Når jeg gemmer som TAB-separeret fil
I excel (hvor du har valgt det område der skal eksporteres ?), du
vælger "gem som .." og vælger "Tekst (tabulatorsepereret) (*.txt)"

>indlæser jeg den derefter i TextWrangler og gemmer derfra med
>Windows CR og Windows Western Latin 1.
>Alt andet viser ikke specialkarakterer korrekt.
Her må jeg gætte lidt, "Windows CR" må være windows linje afslutning
(CR+LF), og tekstenkodningen er " Windows Western Latin 1.".
Dvs. du får konverteret din fil fra mac format til windows format ?

Hvis det er det hele så burde du med en passende macro kunne lave din
importfil til webshoppen fra excel i et hug uden noget med at gemme og
så skulle konverterer i textwrangler.

Har du et eksempel med bare 3 poster/linjer (gerne med en post/linje
der indeholder et specialtegn i teksten feks. æ, ø eller å) fra excel
hvor det er i tab-sepereret format og hvor det er i "efter
textwrangler" format ?

Du kunne også kigge lidt i de gamle filer du har hvor du ikke har
problemer med at importerer data til dey nye webshop, måske du kan
finde et sted hvor apostrof benyttes i noget tekst, det kan måske vise
hvad kuren var ?

Med hensyn til unicode, så er det nok ikke der dit problem er, for den
fil du får lavet fra excel er en flad tekst fil (ikke enkodet), den er
i ANSI format. Efter textwrangler ser det på de eksempler du har vist
i andre poster heller ikke ud til at være konverteret til unicode.


/Hans