Import CSV

I run the below command and it turns the first column in the csv file to
money as the data type. It thinks that the first column is data type of
money.
Any help would be appreciated.
SELECT *
FROM
OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\temp\;Extensions=CSV;',
'SELECT * FROM nessus.csv')It is not clear from your post what the exact question is. Are you loading
the data into a table and is it defaulting to money type? Have you looked
into the actual data in the CSV to see if it has any currency symbols like $
or # associated with the values?
--
Anith

Import CSV

I run the below command and it turns the first column in the csv file to
money as the data type. It thinks that the first column is data type of
money.
Any help would be appreciated.
SELECT *
FROM
OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\temp\;Extensions=CSV;',
'SELECT * FROM nessus.csv')It is not clear from your post what the exact question is. Are you loading
the data into a table and is it defaulting to money type? Have you looked
into the actual data in the CSV to see if it has any currency symbols like $
or # associated with the values?
Anith

Import CSV

I run the below command and it turns the first column in the csv file to
money as the data type. It thinks that the first column is data type of
money.
Any help would be appreciated.
SELECT *
FROM
OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\temp\;Extensions=CSV;',
'SELECT * FROM nessus.csv')
It is not clear from your post what the exact question is. Are you loading
the data into a table and is it defaulting to money type? Have you looked
into the actual data in the CSV to see if it has any currency symbols like $
or # associated with the values?
Anith

Import Column Transformation

I have a SQL Server table that has a "Text" type column. RTF files are stored in this column. On a monthly basis, new or updated RTF files are dropped into a shared folder. I need to upload these into the SQL Server table. Reviewing some of the entries in this forum, I know I probably should be using the Import Column Transformation, but for the last week I can't get it to work successfully.

Another column in the table contains the unique file name(varchar). At runtime the user inputs the fully qualified path to the RTF files which I store in an SSIS variable. Within the package I use a Derived Column Transformation to concatenate these two elements and then pass them as input to the Import Column. I'm expecting the RTF file to come out as output.

How does the Import Column match up the file with the proper table row? Do I need to provide the looping mechanism or does the Import Column handle that?

I've been searching for a working example with no luck. Can someone provide or point to a good sample?

Thanks.

It sounds like you are close to having it working.

The Import Column transform doesn't handle looping itself... it will process multiple rows, though. Each row can specify a different file name. That file will be read and the contents placed in the output column for that row. So, if you have all the file names at once in a table, you can process them in one shot with the Import Column.

However, you may need to be using a foreach loop container in the control flow to get your file names one at a time. In that case, the data flow with your derived column and import column transforms would need to be within the loop as well.

Let me know if this helps.

Thanks
Mark

|||Thanks Mark, that got me further along. I got passed some of the errors I was gettting. I think I'm getting closer after realizing that I need to manually set the "FileDataColumnID". Still having problems with the looping though. Have you done or run across any working examples? I think that would really help. Tom

import column Transform and relative/absolute path

Hi,

I have a table with a BLOB column, and I need to populate this table including the BLOB column (image type in the database).

What I have done is:

1. use a flat file transform to read a .csv file which specifies the names of the files that store the binary contents for the BLOB column for each row.

2. use an Import Column Transform to read the binary files.

3. use an OLE DB Dest transform to dump the data into my destination table.

I got the error saying:

Error: 0xC02090BB at XXXX, Import Column [1]: Opening the file ".\diagram1.bin" for reading failed. The file was not found.

I guess this is because my file "diagram1.bin" is not in the current path? (The current path can be found by "System.IO.Directory.GetCurrentDirectory() call, in my case it is "c:\program files\microsoft visual studio 0\common7\IDE".)

My question is: how to determine the directory path information of the package I am running?

Thanks!

Wenbiao

Absolute paths should be used. Can you use a variable set to the path and then use that variable in an expression to set the root path.|||

Phil Brammer wrote:

Absolute paths should be used. Can you use a variable set to the path and then use that variable in an expression to set the root path.

Thanks Phil for the quick reply, variable works beautifully! I will just have to make sure that I use a configuration file to change the path when I deploy the package.

Thanks again!

Wenbiao

Import Access table to SQL Server

I have an Access 2002 database that I am importing into SQL Server. I have t
rouble with one field type. It a short time in Accws, defined date time and
displayed as hh.mm AM. I can't import this field (I get an overflow message)
. Any ideas.
thans,
StanYou can't directly import it into a smalldatetime or
datetime field as SQL Server doesn't support just a time
field. I can't remember all the details but if you try to
import the column, it uses a date that is out of the range
for SQL Server's destination column. I can't remember the
date Access tries to use but you could try changing the
destination to datetime if it's set to smalldatetime. Or you
may need to use a varchar or some logic for a date
calculation using something like DTS.
-Sue
On Mon, 19 Jul 2004 13:22:02 -0700, "Stanley"
<Stanley@.discussions.microsoft.com> wrote:

>I have an Access 2002 database that I am importing into SQL Server. I have
trouble with one field type. It a short time in Accws, defined date time and
displayed as hh.mm AM. I can't import this field (I get an overflow message
). Any ideas.
>thans,
>Stan

Import Access table to SQL Server

I have an Access 2002 database that I am importing into SQL Server. I have trouble with one field type. It a short time in Accws, defined date time and displayed as hh.mm AM. I can't import this field (I get an overflow message). Any ideas.
thans,
Stan
You can't directly import it into a smalldatetime or
datetime field as SQL Server doesn't support just a time
field. I can't remember all the details but if you try to
import the column, it uses a date that is out of the range
for SQL Server's destination column. I can't remember the
date Access tries to use but you could try changing the
destination to datetime if it's set to smalldatetime. Or you
may need to use a varchar or some logic for a date
calculation using something like DTS.
-Sue
On Mon, 19 Jul 2004 13:22:02 -0700, "Stanley"
<Stanley@.discussions.microsoft.com> wrote:

>I have an Access 2002 database that I am importing into SQL Server. I have trouble with one field type. It a short time in Accws, defined date time and displayed as hh.mm AM. I can't import this field (I get an overflow message). Any ideas.
>thans,
>Stan