Importing C.S.V Files 1

[tforr]

Hello, please can anyone confirm whether it's easy enough to import a c.s.v. file into a Visual FoxPro database table.

Thanks and kind regards,

Tom.

 

[Mike Gagnon]

tforrest

APPEND FROM c:\test.csv DELIMITED

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[tforr]

OK thanks for the quick reply. The statement you have given is great. Im using VFP 6.0. APPEND FROM c:\test.csv DELIMATED. If I want to input the data from the CSV to a specific table in foxpro, what statement will I need to use. i.e I want to import the data from test.csv to employees.dbf.

Hope you can help,

Tom

 

[Mike Gagnon]

tforrest

If the construction is the same (ie your field in your table if the first field in the CSV file) the above statement should work.

select myTable
APPEND FROM c:\test.csv DELIMITED

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[tforr]

Thanks for this Mike. Really appreciate it. I will give it a go.

 

[DSummZZZ]

I know, some times I get nit-picky. So just to clarify. To import test.csv into the employees table, it needs to be open first. If it is you can just select it:

IF !USED('employees')
USE employees in 0
ENDIF
SELECT employees
APPEND FROM test.csv DELIMITED

If the structure of your employees table is such that the records don't line up with the delimited file, you will have to read the text file in and parse each section to add the elements to your table.


-Dave S.-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[tforr]

Do I need to create a new project, database or can I just create the table.

 

[keepingbusy]

Hi all

Can I jump in here and ask, "How can I import an XLS file into a DBF file without using the VisFox wizard?"

I've tried the MSDN example...

IMPORT FROM FileName
[DATABASE DatabaseName [NAME LongTableName]]
[TYPE] FW2 | MOD | PDOX | RPD | WK1
| WK3 | WKS | WR1 | WRK | XLS
| XL5 [SHEET cSheetName]
| XL8 [SHEET cSheetName]
[AS nCodePage]

...but I cant get it to work. The file we have is called newfile.xls and all we're trying to do is create a another file called newfile.dbf by using a command line in a prg

Any suggestions please?

Lee

Alone we can do so little, together we can do so much

 

[Mike Gagnon]

Keepingbusy

...but I cant get it to work

Can you explain what you get as a result (if any) or what error you get?

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[keepingbusy]

Hi Mike

File d:\files\newfile.dbf is not a database

Here is the MSDN code I changed:

IMPORT FROM newfile.xls DATABASE newfile.dbf ;
TYPE XLS

Many thanks
Lee


Alone we can do so little, together we can do so much

 

[Mike Gagnon]

Keepingbusy

You are right, it is not a database, its a table. If you look at the code structure, the parameter you want to use is the next one [NAME] which refers to the name of the table to import the Excel sheet into.
IMPORT FROM FileName
[DATABASE DatabaseName [NAME LongTableName]]
[TYPE] FW2 | MOD | PDOX | RPD | WK1
| WK3 | WKS | WR1 | WRK | XLS
| XL5 [SHEET cSheetName]
| XL8 [SHEET cSheetName]
[AS nCodePage]


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Mike Gagnon]

Keepingbusy

These may help, you may consider using a predefined table and using the APPREND FROM command instead. :

HOWTO: Append Data from a Single Sheet of an Excel Workbook

[ignore]

http://support.microsoft.com/default.aspx?scid=kb;en-us;176894&Product=fox

[/ignore]

PRB: IMPORT/EXPORT Commands Do Not Support CSV File Type

[ignore]

http://support.microsoft.com/default.aspx?scid=kb;en-us;190310&Product=fox

[/ignore]


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[keepingbusy]

Mike

Thank you for your post. The problem I have is that we don't have a predefined table. I'm sure if we created one this would solve the problem, but can you suggest an alternative to "append from" as in some circumstances the data in the Excel file may not always be the same as the fields in the DBF? The wizard works great, but this is no good if you haven't got VisFox installed.

(By the way, we are using version 6)

Lee

Alone we can do so little, together we can do so much

 

[Mike Gagnon]

keepingbusy

Might I suggest then to use Excel Automation to achieve your results. (This depends on the complexity of the worksheets)

#Define xlDBF4	11
Set Step On
oExcel = Createobject("excel.application")
oExcel.displayAlerts = .F.
oWorkbook=oExcel.Workbooks.Open("c:\somesheet.xls")
With oWorkbook
	.SaveAs("C:\someSheet2.dbf",xlDBF4)
Endwith

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[keepingbusy]

Mike

Tried that and I get an error message: OLE IDespatch exception code 0 from Microsoft Excel: Cannot access newfile3.dbf

The highlighted code is:

.SaveAs("d:\files\newfile3.dbf",xlDBF4)

Looking at the problem Mike, I'll think I'll take the option you mentioned previously about creating a pre-defined DBF and see how we get on.

Thank you again for your time
Lee

Alone we can do so little, together we can do so much

 

[Mike Gagnon]

keepingbusy

Looking at the problem Mike, I'll think I'll take the option you mentioned previously about creating a pre-defined DBF and see how we get on.

Suit yourself, if your tried things over and over with the automation sample, you may have locked the table itself, you may need to use CRTL-ALT-DELETE to remove any instances of Excel that are running.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[tforr]

Thanks for everyones feedback over the past couple of days.
My tables have imported the csv files effectively.

Catch you later.

Thomas

 

[keepingbusy]

Mike

I only ran the code you supplied once when I had the error, are you saying that Excel shouldn't be open when your code is run, because if this is he case, Excel wasn't running if that makes sense.

Lee

Alone we can do so little, together we can do so much

 

[Mike Gagnon]

Lee

No, I was suggesting that if you tried the above code more than once, and since the code does not include the portion to close Excel and clear the oExcel object that you instanciated, that you might be left with instances of Excel that are locking files.
I'm surpised that the suggested code does not work for you, as I tride it on two different systems and I do not get the error you mention. What version of Excel are you using?

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[keepingbusy]

Mike

Microsoft Excel 2000 and I see what you say, so I'll give it another go.

Many thanks
Lee

Alone we can do so little, together we can do so much