CSV to DBF

[ali]

Hi!

My name is Aljoscha. I'm starting to Programming FiveWin. I have a question to you. I have a CSV file and I would like create a Programm that read this file and save this to a DB.

I think this is not to difficult but I have no idea. So I hope someone can help me.

Best Regards
Aljoscha

[George]

Hello Aljoscha,
You can use the code below to append your CSV file to your DBF.
It's not necesary to read the CSV, as doing this is very slow.

Code: Select all | Expand

USE ("YOUR_DBF_TABLE")       
dbzap()  // Delete records 
dbpack()  
APPEND FROM "YOUR_CSV_FILE.CSV" DELIMITED WITH (,)

Regards

George

[ali]

Hi George! Thank you for your quick answer. Is it possible that this works also with xlsx files?

Best Regards
Aljoscha

[fafi]

Hi George! Thank you for your quick answer. Is it possible that this works also with xlsx files?

Best Regards
Aljoscha

Chose file save as to DBF file ... ->( Excel application

regards
fafi

[Gale FORd]

I think .xlsx files are Excel 2007 or later. That version does not save to .dbf files anymore.

[nageswaragunupudi]

I think .xlsx files are Excel 2007 or later. That version does not save to .dbf files anymore.

Yes. In that case, save as CVS and APPEND FROM CVS.

[ali]

Hi !

I have used the code you have written but it don't works. There is my code and a sample of the CSV

Code: Select all | Expand

function mkdbf()                                       

//Sample CSV
//01.01.2010;31.01.2010;DZ OG;4050873;KM;4049532;DZ OG;4050873;8142004476;Tembl;1140229;;FT Gl.Eq.Str.A acc EUR-H;LU0358320330;Afs;F T I Fu;1140229-003;02.02.2010;31.01.2010;01.01.2010;BAR;Eu;Up;EUR;1;-3032;-3200;-137,96;1;-137,96;1;-137,96
//01.01.2010;31.01.2010;DZ OG;4050873;KM;4049532;DZ OG;4050873;8142004476;Tembl;1152332;;FT Gl.Eq.Str.A acc EUR-H;LU0358320330;Afs;F T I Fu;1152332-001;15.01.2010;31.01.2010;31.01.2010;BAR;En;Up;EUR;1;3200;3200;-25;1;-25;1;-25

                                                       
   DbCreate("c:\@tmp\test",;                           
      { {"F1"  , "C",  30,    0} ,;                    
      {"F2", "C",  30,   0} ,;                         
        {"F3", "C",  30,   0} ,;                         
        {"F4", "N",  30,   0} ,;                         
        {"F5", "C",  30,   0} ,;                         
        {"F6", "N",  30,   0} ,;                         
        {"F7", "C",  30,   0} ,;                         
        {"F8", "C",  30,   0} ,;                         
        {"F9", "C",  30,   0} ,;                         
        {"F10", "C",  30,   0} ,;                        
        {"F11", "C",  30,   0} ,;                        
        {"F12", "C",  30,   0} ,;                        
        {"F13", "C",  30,   0} ,;                        
        {"F14", "C",  30,   0} ,;                        
        {"F15", "C",  30,   0} ,;                        
        {"F16", "C",  30,   0} ,;                        
        {"F17", "C",  30,   0} ,;                        
        {"F18", "C",  30,   0} ,;                        
        {"F19", "C",  30,   0} ,;                        
        {"F20", "C",  30,   0} ,;                        
        {"F21", "C",  30,   0} ,;                        
        {"F22", "C",  30,   0} ,;                        
        {"F23", "C",  30,   0} ,;                        
        {"F24", "C",  30,   0} ,;                        
        {"F25", "C",  30,   0} ,;                        
        {"F26", "C",  30,   0} ,;                        
        {"F27", "C",  30,   0} ,;                        
        {"F28", "C",  30,   0} ,;                        
        {"F29", "C",  30,   0} ,;                        
        {"F30", "C",  30,   0} ,;                        
        {"F31", "C",  30,   0} ,;                        
      {"F32", "C",  30,    0} }   )                    
                                                       
msginfo("DB OK")                                       
                                                       
USE ("c:\@tmp\test") NEW ALIAS  tmp
dbzap()  // Delete records                             
dbpack()                                               
APPEND FROM "c:\import\201001.csv" DELIMITED WITH (";")
                                                       
msginfo("IMPORT OK")                                   

return nil
 

I hope someone can help me

Regards

Aljoscha

[xProgrammer]

Hi Aljoscha

A CSV file is separated by commas not semi colons as in your data. I know that you set the DELIMITED option to a semi colon but the delimiter character is the character that encloses character fields. The character fields in your data are enclosed with double quotation marks which is the default.

A check of the documentation reveals that you can specify the field delimiter as well as the character string delimiter by using a two element array enclosed in parentheses. In the case of APPEND FROM this must be the last clause.

On that basis I believe that the following line should do what you want

Code: Select all | Expand

APPEND FROM "c:\import\201001.csv" DELIMITED WITH ( { '"', ";" } )

I hope that helps

Regards
xProgrammer

[ali]

Thank you for your fantastic answer. It works.

[modicr]

Hello!

BTW, does APPEND FROM .csv support multiline strings (CRLFs between quatition marks)?

Thanks, Roman

[xProgrammer]

Hi Roman

The best way is to try it and see. It isn't specified as far as I can see in the documentation and it would depend how it was implemented. You could try to follow the source code but testing would be simpler.

If it doesn't work you might need a workaround. If you read your csv file into a single (large?) character variable you could then manipulate it along the following lines:

It would be hard to single out the CRs inside fields but the record terminating ones would presumably be something like:

'"' +Chr(13) + Chr(10) + '"'

You could use StrTran() function to replace all the with an alternative sequence, say

'"' +'~' + Chr(10) + '"'

(You could use any sequence not contained in your data set)

Then use StrTran to replace all remaining carriage returns with a character - or series of characters, that doesn't occur in your data - possibly the best choice woulkd be to replace

Chr(13) + Chr(10)

with

Chr(141) + Chr(10)

along the lines of MemoEdit() and HardCR()

Then you would revers the original StrTran changing

'"' +'~' + Chr(10) + '"'

back to

'"' +Chr(13) + Chr(10) + '"'

Then you could write the string back out to a file which should import normally.

Then you might want to cycle through the resultant table and use StrTran() to change the

Chr(141) + Chr(10)

back to

Chr(13) + Chr(10)

which you can do with StrTran() or HardCR(). But you might be better off doing this dynamically when you are displaying the data.

Above is a general guide only - you would need to review and adapt according to the nature of your data file. And even better you may not need a workaround at all


Regards
xProgrammer