The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
I am looking for an example to import directly from a CSV file.
Posted: 09 Nov 07 7:26 AM
|
| This CSV file contains text, numbers, and date fields. I found an example to import from an Excel file here that works, but I want to avoid having to open Excel first to convert the CSV file to XLS. Thanks in advance for you assistance. |
|
|
| |
| |
|
Re: I am looking for an example to import directly from a CSV file.
Posted: 11 Nov 07 5:06 PM
|
Yes, I have small csv file that needs to be imported by a button in SLX. The file has a ID as a text field without quotes, followed by some money or number fields and the last field is a date field formatted in quotes like "9/30/2007".
Any assistance is greatly appreciated. |
|
|
|
Re: I am looking for an example to import directly from a CSV file.
Posted: 11 Nov 07 5:28 PM
|
Yes, I have small csv file that needs to be imported by a button in SLX. The file has a ID as a text field without quotes, followed by some money or number fields and the last field is a date field formatted in quotes like "9/30/2007".
I only have a sample to import an XLS file and not a CSV file. The source can only export as fixed width, Tab delimited or CSV. I am trying to avoid having to convert from XLS to CSV in Excel by reading the CSV file directly.
Any assistance is greatly appreciated. |
|
|
|
Re: I am looking for an example to import directly from a CSV file.
Posted: 11 Nov 07 5:28 PM
|
Yes, I have small csv file that needs to be imported by a button in SLX. The file has a ID as a text field without quotes, followed by some money or number fields and the last field is a date field formatted in quotes like "9/30/2007".
I only have a sample to import an XLS file and not a CSV file. The source can only export as fixed width, Tab delimited or CSV. I am trying to avoid having to convert from XLS to CSV in Excel by reading the CSV file directly.
Any assistance is greatly appreciated. |
|
|
|
Re: I am looking for an example to import directly from a CSV file.
Posted: 11 Nov 07 8:54 PM
|
Create your connection & read from file (filespec held in FilePath and Filename)
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FilePath & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
objRecordset.Open "SELECT * FROM [" & Filename & "]", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
Now you have a recordset and can iterate round it, creating the SLX records as you go. Make sure that you Include System: SLX Database Support in your import script.
Note: your CSV file needs fieldnames in row 1 - best to make these 'nice' - concise, no spaces & no unusual characters. These fieldnames will be inherited by the recordset.
Note 2: importing from CSV is far easier than from Excel, in my opinion. Don't even think about converting CSV to XLS format and then importing.
Note 3: if your SQL is hot, you can import directly from T-SQL using OPENROWSET. Don't do this unless you understand the synchronisation consequences (or if you have no remotes).
|
|
|
|
Re: I am looking for an example to import directly from a CSV file.
Posted: 12 Nov 07 9:47 AM
|
Thanks Phil, this worked great. I just need to trap for a couple of errors caused by bad data.
I have a question about Note3. First, we currently do not have remotes. This CSV file is being imported into a staging table that would not be synced to remotes. I originally used a DTS script to import this daily, but they now want to run this any time from in SLX. Is there a way to call my DTS from inside SLX?
I just read about OPENROWSET. This looks like another way to get the CSV data into the staging table. I will test this out too.
Thanks again. |
|
|
|
Re: I am looking for an example to import directly from a CSV file.
Posted: 12 Nov 07 4:36 PM
|
Check Books Online regarding the T-SQL DTSRun command - it will (in theory - never tried it) allow you to run DTS packages from SLX.
As far as OPENROWSET is concerned, here's a code snippet I got from a forum that might set you in the right direction. Obviously the appeal of this method is the ease of deployment and maintenance:
SELECT *
FROM
OPENROWSET('MSDASQL',--provider name (ODBC)
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\;Extensions=CSV;',--data source
'SELECT * FROM sample.csv')
PP |
|
|
|