FiveTech Software tech support forums

Posted: **Sat Mar 06, 2010 8:33 pm**

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

Posted: **Sun Mar 07, 2010 3:50 am**

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

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

Regards

George

Posted: **Sun Mar 07, 2010 4:55 am**

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

Best Regards
Aljoscha

Posted: **Sun Mar 07, 2010 6:52 am**

ali wrote: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

Posted: **Mon Mar 08, 2010 12:18 am**

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

Posted: **Mon Mar 08, 2010 6:27 am**

Gale FORd wrote: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.

Posted: **Tue Mar 16, 2010 9:48 am**

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


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

Posted: **Tue Mar 16, 2010 10:15 am**

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

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

I hope that helps

Regards
xProgrammer

Posted: **Tue Mar 16, 2010 10:32 am**

Thank you for your fantastic answer. It works.

Posted: **Tue Jul 13, 2010 11:29 am**

Hello!

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

Thanks, Roman

Posted: **Wed Jul 14, 2010 9:50 am**

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

FiveTech Software tech support forums

CSV to DBF

CSV to DBF

Re: CSV to DBF

Re: CSV to DBF

Re: CSV to DBF

Re: CSV to DBF

Re: CSV to DBF

Re: CSV to DBF

Re: CSV to DBF

Re: CSV to DBF

Re: CSV to DBF

Re: CSV to DBF