topic-18285

Posts: 23

Joined: Wed May 07, 2008 02:50 PM

CSV to DBF
Posted: Sat Mar 06, 2010 08: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

George

Posts: 729

Joined: Tue Oct 18, 2005 06:49 PM

Re: CSV to DBF
Posted: Sun Mar 07, 2010 03: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 (fw): Select all Collapse

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

Regards

George

ali

Posts: 23

Joined: Wed May 07, 2008 02:50 PM

Re: CSV to DBF
Posted: Sun Mar 07, 2010 04:55 AM

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

Best Regards
Aljoscha

fafi

Posts: 169

Joined: Mon Feb 25, 2008 02:42 AM

Re: CSV to DBF
Posted: Sun Mar 07, 2010 06: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

Gale FORd

Posts: 663

Joined: Mon Dec 05, 2005 11:22 PM

Re: CSV to DBF
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.

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: CSV to DBF
Posted: Mon Mar 08, 2010 06: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.

Regards

G. N. Rao.

Hyderabad, India

ali

Posts: 23

Joined: Wed May 07, 2008 02:50 PM

Re: CSV to DBF
Posted: Tue Mar 16, 2010 09: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 (fw): Select all Collapse

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

Posts: 464

Joined: Tue May 16, 2006 07:47 AM

Re: CSV to DBF
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 (fw): Select all Collapse

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

I hope that helps

Regards
xProgrammer

ali

Posts: 23

Joined: Wed May 07, 2008 02:50 PM

Re: CSV to DBF
Posted: Tue Mar 16, 2010 10:32 AM

Thank you for your fantastic answer. It works.

modicr

Posts: 207

Joined: Fri Oct 07, 2005 07:58 AM

Re: CSV to DBF
Posted: Tue Jul 13, 2010 11:29 AM

Hello!

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

Thanks, Roman

xProgrammer

Posts: 464

Joined: Tue May 16, 2006 07:47 AM

Re: CSV to DBF
Posted: Wed Jul 14, 2010 09: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 Support Forums

Continue the discussion