import of multiple CSV files in one MS SQL table

Hi all,

I have de following application to do :

I receive several .csv files from another application in a determined folder
of my PC.

Those files are named with the format log1.csv logs2.csv logs...
The number of file is variable but the internal format is always : time_sec;level
So the files content a field that may be used as unique key in the target database.

I'm trying to build a DTS package that should import periodically
all the CSV's present in the folder and then destroy them if done
successfully.

Apparently its not so simple than I supposed. I have always to give the name
of the table I want to import.

any idea?"Laurent" <autplc@.hotmail.com> wrote in message
news:e8bac87e.0411100552.5af177ca@.posting.google.c om...
> I receive several .csv files from another application in a determined
folder
> of my PC.
> Those files are named with the format log1.csv logs2.csv logs...
> The number of file is variable but the internal format is always :
time_sec;level
> So the files content a field that may be used as unique key in the target
database.
>
> I'm trying to build a DTS package that should import periodically
> all the CSV's present in the folder and then destroy them if done
> successfully.

Two Options:

Option 1:
Rename the file to a temporary name (in an ActiveX task) and use the
temporary name for the Data Pump

Option 2:
Use a Dynamic Properties Task to change the Data Source Name in the Data
Pump Task.

Regards,
Jim

Import multiple csv into multiple tables

Is there a way to import multiple csv files from a directory into sql
2005? The situation I have right now is that I have a folder with
multiple csv files that i need to import into sql 2005. I can do it
with the import wizard but it takes to long. The files will be updated
monthly. The first row in the files contains all the header information
which may change monthy. What I am looking to do is import all of these
csv into tables. One csv file into for one table. Ideally I would like
to use the name of the csv file as the name of the table. Any bump in
the right direction would be apprecietedChicagoboy27 (jeremy.bird@.gmail.com) writes:

Quote:

Originally Posted by

Is there a way to import multiple csv files from a directory into sql
2005? The situation I have right now is that I have a folder with
multiple csv files that i need to import into sql 2005. I can do it
with the import wizard but it takes to long. The files will be updated
monthly. The first row in the files contains all the header information
which may change monthy. What I am looking to do is import all of these
csv into tables. One csv file into for one table. Ideally I would like
to use the name of the csv file as the name of the table. Any bump in
the right direction would be apprecieted

You could use BCP or BULK INSERT, but it appears that you would have to
add quite some control code on top that.

A better alternative could be to turn to SQL Server Integration Services,
which is what the Import Wizard uses. Unfortunately, though, I am
completely unexperienced myself with SSIS, so I cannot assist further.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

IMPORT Multiple CSV Files to SQLSERVER Table

Dear All,

I am importing all the files from a particular folder to a table on my database KB. It is working perfectly if i use it on the same system where the DB exists and not working from the network.

USE TESTDB

--Table Creation Starts here

Create table Account([ID] int IDENTITY PRIMARY KEY, Name Varchar(100),
AccountNo varchar(100), Balance money)

Create table logtable (id int identity(1,1),
Query varchar(1000),
Importeddate datetime default getdate())

--Table Creation ends here

--Stored Procedure Starts here

Create procedure usp_ImportMultipleFiles @.filepath varchar(500),
@.pattern varchar(100), @.TableName varchar(128)
as
set quoted_identifier off
declare @.query varchar(1000)
declare @.max1 int
declare @.count1 int
Declare @.filename varchar(100)
set @.count1 =0
create table #x (name varchar(200))
set @.query ='master.dbo.xp_cmdshell "dir '+@.filepath+@.pattern +' /b"'
insert #x exec (@.query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @.max1 = (select max(ID) from #y)
--print @.max1
--print @.count1
While @.count1 <= @.max1
begin
set @.count1=@.count1+1
set @.filename = (select name from #y where [id] = @.count1)
set @.query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")'
--print @.query
exec (@.query)
insert into logtable (query) select @.query
end

drop table #y

--sp ends here

Exec usp_ImportMultipleFiles 'c:\myimport\', '*.csv', 'Account'

If i use the above Exec like

Exec usp_ImportMultipleFiles '\\kb-02\C$\MyImport\', '*.csv', 'Account'
I am getting the following error:

Could not bulk insert because file '\\kb-02\C$\MyImport\Access is denied.' could not be opened.
Operating system error code 5(Access is denied.).

C Drive and MyImport folder is shared on system kb-02

Would appreciate your valuable HELP.

thanking your valuable help in advance.
K006BMy guess would be that the NT Login being used by your SQL Server service doesn't have access to \\kb-02\c$ (which is a good thing). Try creating an explicit share and giving permission to the appropriate NT Login.

-PatP|||After SP3 the security context of the user executing XP_CMDSHELL is validated before it's executed in the context of SQL Server service account. Also, if the service is running under Local System, then NO NETWORK ACCESS IS ALLOWED, period. The service needs to run under a Domain User account, and the user that executes the XP_CMDSHELL needs to have sysadmin permission to successfully complete the operation. There is a way to avoid this by creating a scheduled task and then invoking it with sp_start_job. This also requires SQLAgent service to run under Domain Users account with WRITE privileges to the share, but does not require the invoking user to have anything special, - just EXECUTE permission to sp_start_job which is given to PUBLIC by default.

Import issue for CSV file with quotes around all data fields

Hello!

I have a CSV file that encloses all the data fields with quotation marks. Here is a sample:

"08/01/2007","3","021200012","123","0.03"

Is there any way in SSIS that I can tell the Flat File wizard to ignore the quotation marks? I don't want to import the quotes in the database since that will really mess up other applications that need to use the data.

Thanks in advance,

Harry

I'm running into the same issue. I was going to post and I saw your posting. Though mine is slightly different. I have several fields on my .csv file, some fields have quotes some do not and some data values have quotes and some do not. So my line looks like this:

NISSAN,"NEW", "Smith, John"

or some look like this

NISSAN, NEW, Smith, Michelle

So i need something to remove the quotes as well so I don't see them in my table, only the data

|||

Got the answer...put the " (quotes) in the text qualifier instead of the default of <none>.

|||

Big H wrote:

Got the answer...put the " (quotes) in the text qualifier instead of the default of <none>.

I have that but because not all of my data is surrounded by quotes, its still failing for me on the insert into the table

|||

Hi,

You can put a script component and then clean the fields that you need to clean by using any string methods.

Hope that helps

Cheers

Rizwan

|||

How? I'm new to this SSIS process and I'm learning as I go. So how would a script componet 'clean' the fields?

|||

IGotyourdotnet wrote:

How? I'm new to this SSIS process and I'm learning as I go. So how would a script componet 'clean' the fields?

Isn't all of your "text" data surrounded by quotes? "Proper" CSV formatting would have text fields surrounded by quotes and numeric fields not surrounded by quotes.

If you can, move away as fast as you can from CSV files. It's a terrible format, especially if you run into situations where you have embedded quotes in your text fields. Tab delimited or fixed width are better alternatives. Or even XML.

|||

Phil Brammer wrote:

IGotyourdotnet wrote:

How? I'm new to this SSIS process and I'm learning as I go. So how would a script componet 'clean' the fields?

Isn't all of your "text" data surrounded by quotes? "Proper" CSV formatting would have text fields surrounded by quotes and numeric fields not surrounded by quotes.

If you can, move away as fast as you can from CSV files. It's a terrible format, especially if you run into situations where you have embedded quotes in your text fields. Tab delimited or fixed width are better alternatives. Or even XML.

Isn't all of your "text" data surrounded by quotes? No, its generated by another process (I believe an Oracle process)and the SSIS (former DTS) packages grabs the files and inserts the data into the SQL tables.

|||

IGotyourdotnet wrote:

Isn't all of your "text" data surrounded by quotes? No, its generated by another process (I believe an Oracle process)and the SSIS (former DTS) packages grabs the files and inserts the data into the SQL tables.

But *some* of your text data has quotes?|||

correct, it could be all of it at times or some of it at times. So I could see it like

row 1 NISSAN, Smith John, "NEW"

row 2 NISSAN, "Smith Michelle", NEW

or

row 1 "NISSAN", "Smith John", NEW

row 2 "NISSAN", Smith John, NEW

or

row 1 "NISSAN", "Smith" John, NEW"

row 2 "NISSAN", "Smith John", "NEW"

so far I've see all of the above in this file.

|||

You should be able to use a Derived Column transform to strip the quotes off.

Code Snippet

REPLACE([Column 0],"\"","")

import file question

suppose i would receive a file in csv format daily like this:

cvg_20070516.csv

cvg_20070517.csv

cvg_20070518.csv

.

.

.

so how can i import the data into the database as i can't specifcy a file to be the source file? (which means, for example, after i hv got a file cvg_20070518.csv, how can i set up an automation that to save another copy call 'cvg.csv' in another folder and so i can use this file as a source to import into database?)

thanks a lot, i appreciate your help!

Here you go...

Code Snippet

Create Table #Files

(

CSVFilevarchar(100)

);

Declare @.File as Varchar(100);

Declare @.cmd as varchar(1000);

Insert Into #Files

Exec master..xp_cmdshell 'dir /B C:\data\csv\*.csv'

Select @.File = 'C:\data\csv\' + Max(CSVFile) from #Files

Set @.cmd = 'Copy /Y ' + @.File + ' C:\data\csv\importable\cvg.csv'

Exec master..xp_cmdshell @.cmd

Drop table #Files;

|||

it returns the result like this:

Output

The system cannoot find the filespecified

null

can someone lese help...

|||

You have to give the proper path.

The example shows the sample path...

You can make select query against the temp table to verify all the files are listed ...

Import Domino data to SQL Server

We are going to do a conversion from a Domino server to a SQL Server
database. DTS is the obvious choice for importing data from a .csv or
similar into SQL Server, but is there a recommended way of automating the
export of the Domino data to a set of .csv or similar?
I'd love to connect directly using ODBC/OLE DB, but I'm not sure if that is
an option.
Any guidance would be appreciated. Thanks in advance.
Mark
Hi Mark,
Yes, your Domino could connect directly to SQL Server via ODBC Driver / OLE
DB provider.
Based on my scope, NotesSQL is an ODBC (Open Database Connectivity) driver
for Notes and Domino. With NotesSQL, end users and application developers
can integrate Domino data with their applications using tools such as
Access and SQL Server. See the following links for more detailed information
Lotus NotesSQL
http://www.lotus.com/products/produc...84085256e20006
db691?OpenDocument
With NotesSQL, you are able to DTS directly
Hope it helps and if you have any questions or concerns, don't hesitate to
let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Mark,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Import Domino data to SQL Server

We are going to do a conversion from a Domino server to a SQL Server
database. DTS is the obvious choice for importing data from a .csv or
similar into SQL Server, but is there a recommended way of automating the
export of the Domino data to a set of .csv or similar?
I'd love to connect directly using ODBC/OLE DB, but I'm not sure if that is
an option.
Any guidance would be appreciated. Thanks in advance.
MarkHi Mark,
Yes, your Domino could connect directly to SQL Server via ODBC Driver / OLE
DB provider.
Based on my scope, NotesSQL is an ODBC (Open Database Connectivity) driver
for Notes and Domino. With NotesSQL, end users and application developers
can integrate Domino data with their applications using tools such as
Access and SQL Server. See the following links for more detailed information
Lotus NotesSQL
http://www.lotus.com/products/produ...584085256e20006
db691?OpenDocument
With NotesSQL, you are able to DTS directly
Hope it helps and if you have any questions or concerns, don't hesitate to
let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Mark,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Import Domino data to SQL Server

We are going to do a conversion from a Domino server to a SQL Server
database. DTS is the obvious choice for importing data from a .csv or
similar into SQL Server, but is there a recommended way of automating the
export of the Domino data to a set of .csv or similar?
I'd love to connect directly using ODBC/OLE DB, but I'm not sure if that is
an option.
Any guidance would be appreciated. Thanks in advance.
MarkHi Mark,
Yes, your Domino could connect directly to SQL Server via ODBC Driver / OLE
DB provider.
Based on my scope, NotesSQL is an ODBC (Open Database Connectivity) driver
for Notes and Domino. With NotesSQL, end users and application developers
can integrate Domino data with their applications using tools such as
Access and SQL Server. See the following links for more detailed information
Lotus NotesSQL
http://www.lotus.com/products/product4.nsf/wdocs/3243f3d81944584085256e20006
db691?OpenDocument
With NotesSQL, you are able to DTS directly:)
Hope it helps and if you have any questions or concerns, don't hesitate to
let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Mark,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Import datetime

I am having a problem importing data from a csv file. I am trying to import into a table with numeric and datetime columns, but it errors out everytime. Is there a way to accomplish time? I am not very familiar with Microsoft SQL, so hopefully this can be accomplished.

I am not sure how to approach this. Any help is greatly appreciated. Thanks in advance.What are you using? DTS, BCP, BULK INSERT?

What errors are you getting?|||Trying just normal import. Right click database -> All Tasks -> Import Data.
Going from csv file into already established table. I have attached the error box.|||Import it to a new table...let dts create the table for you, then check out the datatypes...sounds like your mappings are off...

I usually always create a staging table for imports...that way I can audit/sanitize the input files.

If tere's something wrong you can stop it there, BEFORE you hose the good data|||You get bad data ?!?!

Oh the shame! Your users don't always provide you with nice, clean, carefully QA'ed data in their files? Especially CSV files ?!?

Ok, so I'll climb down off of my soap box now...

As Brett observed, it is a lot safer to just bring the data wholesale into a staging table, so that you can inspect it carefully, and if it is found wanting you can dump it without a second thought. I generally presume that data is absolutely worthless until after I prove otherwise. More often than not I'm wrong, but not a whole lot more often!

-PatP|||Actually, the data in question is the original data from the table. Someone decided to screw with constraints to allow some new data to be imported and ended up with duplication. Due to some other constraint, the duplicates could not be deleted (I didn't see the error). So the data should be OK.
The import utility won't take a field from the csv file (that was exported from that table) and import it back into the numeric or datetime field in the table.|||Whenever I do a raw import from a CSV file, I get the columns processed in ascending order. Based on the error message that you posted, you appear to be using a transformation that processes the columns in descending order.

Are you using a custom transformation? If not, which Enterprise Mangler / SQL Service pack are you using?

You also processed 91 rows Ok, and 92 went "toes skyward" on you. I'd investigate the CSV file, looking hard at row 92.

-PatP|||Thank you all for your help. Too much frustration made me rush through things. Come to find out, after importing the csv file into a new table (all columns vchar), the users inputed commas in some of the descriptions (one of the columns). This of course forced additional columns to be created and unmatched datatypes within certain columns.
Being the only DBA here for MSSQL, Oracle, Sybase, and MySQL, I have no one to work through these things with locally.
Thanks, to the forums and your responses.|||You are NOT alone

"mind what you learn here...save you it will..."

Oh, and "trust no one Dr. Jones..."

import data question (reask)

suppose i would receive a file in csv format daily like this:

cvg_20070516.csv

cvg_20070517.csv

cvg_20070518.csv

.

.

.

so how can i import the data into the database as i can't specifcy a file to be the source file? (which means, for example, after i hv got a file cvg_20070518.csv, how can i set up an automation that to save another copy call 'cvg.csv' in another folder and so i can use this file as a source to import into database?)

some one replyed me with the following solution

Create Table #Files

(

CSVFile varchar(100)

);

Declare @.File as Varchar(100);

Declare @.cmd as varchar(1000);

Insert Into #Files

Exec master..xp_cmdshell 'dir /B C:\data\csv\*.csv'

Select @.File = 'C:\data\csv\' + Max(CSVFile) from #Files

Set @.cmd = 'Copy /Y ' + @.File + ' C:\data\csv\importable\cvg.csv'

Exec master..xp_cmdshell @.cmd

Drop table #Files;

but it doesn't work. can someone elaborate more on it and tell me how's it gonna work?

Having seen the error message in your other message, it sounds like the account under which your SQL Server instance's service is running doesn't have the appropriate permissions on the folder and/or CSV files.

Chris

import data properly from csv file.

I need to extract data from a csv file, validate it, and populate other
tables with that data for a multi user web application.
I am importing a csv file via linked servers as follows:
EXEC('SELECT * into ##temptbl FROM '+@.linked_server + '...['+@.file + '#' +
@.extension + ']')
Once data gets into ##temptbl then I do proper validation and populate other
tables.
This will not work if there are other users importing the file as well
because of global temp table ##temptbl.
Do I create a separate physical table to populate and delete based on
certain criteria for that user?
I tried using table variable inside the dynamic sql but did not work. So my
best bet for now is
to have a physical table, populate it for certain criteria, do validation,
and populate other permanent tables. After
successful population I would go ahead and delete rows this temporary
staging for certain criteria.
Does this make sense or this approach stinks?
TIA...I would really appreciate if any guru/expert could address this.
TIA...
"sqlster" wrote:

> I need to extract data from a csv file, validate it, and populate other
> tables with that data for a multi user web application.
> I am importing a csv file via linked servers as follows:
> EXEC('SELECT * into ##temptbl FROM '+@.linked_server + '...['+@.file + '#' +
> @.extension + ']')
> Once data gets into ##temptbl then I do proper validation and populate oth
er
> tables.
> This will not work if there are other users importing the file as well
> because of global temp table ##temptbl.
> Do I create a separate physical table to populate and delete based on
> certain criteria for that user?
> I tried using table variable inside the dynamic sql but did not work. So m
y
> best bet for now is
> to have a physical table, populate it for certain criteria, do validation,
> and populate other permanent tables. After
> successful population I would go ahead and delete rows this temporary
> staging for certain criteria.
> Does this make sense or this approach stinks?
> TIA...

Import data into SQL Server

Hi guys,

I am looking to import data into the SQL Server database, from a CSV file..

anyone with any suggestions for how to start with it ??

Thanks heapsUse DTS.Here is a link to a Google groups listing, which also mentione using BPC.|||Thanks a lot mate... ur a gem|||Just one problem...

How do i call that DTS Saved job ?|||Is this a one-time job or something that needs to be run regularly? If a one time, just select the job in Enterprise Manager and I believe if you right click on it you will be able to run it (not near EM just now for details).

import data from email attachment

This ones way beyond me, I don't even know where to start. basically we get an email every day with an attachment. The attachment is a csv file. I'd like to import that file into a table in sql. Anyone have any ideas?

Look into SSIS (SQL Server Integration Services)

That would give you everything you need to import the file.

BobP

|||Thanks... I started researching based on your post, and came accross xp_startmail and xp_readmail. These seem to be exactly what i was looking for....except;
xp_startmail gives me the following error;
Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.
*** Start mail failed ***

I check outlook and it is the default. I'm using sql server 2005 outlook 2003. I have 2 mail accounts set up on the server, both accounts are bogus accounts that untill now I've used to send mail only (sp_senddbmail). I think I may have to create a real email account that has a corresponding windows account?

With that in mind I tried running xp_startmail using my windows login information and received the same error... ie
xp_startmail 'username', 'password'

|||

Another approach...pros and cons to everything....

Have outlook automatically save any file attachments to a specified folder (see http://www.vbaexpress.com/kb/getarticle.php?kb_id=522 )

Then, in a seperate process, use SSIS or some other mechanism to import the contents of the file.

In my opion, SQL is best when dealing with data rather than email.

Just my $0.02.

Import data from csv file

I am trying to import a csv file using the import wizard. The file is 2.5 gig and I have the entire (almost 4gig) database available. I keep getting an error that I do not have enough file space. Is the flat file size not an apple to apple match for database size? Any insite would be great! Thanks in advance.

A nnn MB or GB file imported into SQL Server won't result in an exact size increase in SQL Server. There is no one formula that will give you the exact size needed to import the file. It depends on too many variables such as data types in the table, indexes, etc. But...I would suspect it's the logging of the import if you are in full recovery and are just loading this through the wizard. Normally, you would want to import that in batches or using a bulk import/load with the space you have in the database. If you need to use the wizard and can use a query to break this up in "chucks" of data, You may want to look at importing 20 - 25 % of the records, backup the log. Import another 20 - 25% of the records, backup the log, etc. You also would want to make sure your data and log files aren't set to grow by percentages - you don't have much control over the growth of the files if you have the files set to grow in percentages.

-Sue

|||

What is the size of data file and transaction log on this 4gb database?

As explained the size of data within SQL server depends on the datatype used and number of rows.

Import csv files to Sql Server problem

Hi,

I try to import csv files to Sql Server using .net. The code is as following:

string strCsvConn =@."Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\;Extended Properties='text;HDR=Yes;FMT=Delimited(,)';";

using (OleDbConnection cn =newOleDbConnection(strCsvConn))

{

string strSQL ="SELECT * FROM " + strFileName;

OleDbCommand cmd =newOleDbCommand(strSQL, cn);

cn.Open();

using (OleDbDataReader dr = cmd.ExecuteReader())

{

// Bulk Copy to SQL Server

using (SqlBulkCopy bulkCopy =newSqlBulkCopy(strSqlConn))

{

bulkCopy.DestinationTableName = strSqlTable;

bulkCopy.WriteToServer(dr);

}

}

}

And the data is as following (simplified):

Model,Serial

AFICIO 3045,K9464900965

AFICIO 3045,K9464900932

Fax 5510L,A3761290041

Fax 2210L,A4978800008

AFICIO 3025,K8565201014

AFICIO 3025,K8565102398

The result of the 2nd column is: 9464900965, 9464900932, null, null, 8565201014, 8565102398 - either the first character is missing or the whole entry is missing.

One more weird thing is that some other files work fine, though I am not able to tell any difference between them.

Any idea is hoghly appreciated.

shz

Hmmm... what's the datatype / size of the second column in your destination database?

In your example, the two rows that get null in second column contains a space in the first column. Is it maybe because space is used as a separator too in some way?|||

Thanks johram,

The datatype is varchar(50). However, I am afraid it has nothing to with the database, because it is the DataReader that retrieves wrong data. I test the DataReader with the following code:

while (dr.Read())

{

string str =Convert.ToString(dr[1]);

}

And all entries in the 1st column contain a space. The file contains more than 10 columns actually, all other columns are good.

Some more findings:

I have some "good" files that work fine and some "bad" files that have this problem - I cannot tell any difference between them in terms of data format. If I copy some records from a "good" file to a "bad" file, those records become bad. If I copy some records from a "bad" file to a "good" file, those records become good.

If I use TDS to import the files to SQL Server, it works fine. Excel can open the files properly too.

Thanks,

shz

|||

It seems to be the problem in the header (columns) of the cvs file.

Please make sure thay are okay.

Good luck.

|||

Fixed - need a Schema.ini file to define the Extended Properties of the driver.

Thanks to everyone.

Import CSV Files to SQL Server Null Values Problem

Hello,
I am trying to import a CSV file into my SQL Server database, this file was originally generated by another database table (on another server) with the same structure, the table contains two columns ofrealdatatype withAllow Null Valuesetto true for those columns, the CSV file contains the valueNULLfor theses columns, I am facing a problem when importing this file. This may be because DTS tries to represent values as strings then to convert them torealdatatype which results in transforming the value "NULL" toreal,I receive an error message saying.
Error during Transformation 'DirectCopyXform' for Row number 1. Errors encountered so far in this task: 1.

TransformCopy 'DirectCopyXform' conversion error: Conversion invalid for datatypes on column pair 8 (source column 'Col008' (DBTYPE_STR), destination column 'zip_longitude' (DBTYPE_R4)).

TransformCopy 'DirectCopyXform' conversion error: Conversion invalid for datatypes on column pair 7 (source column 'Col007' (DBTYPE_STR), destination column 'zip_latitude' (DBTYPE_R4)).
How can I work around this problem?
Any help would be appreciable

Move the data into temp table then destination and I think you should use Float instead of Real because most of the T-SQL functions dealing with Longitude and Latitude are in Float. And yes Float is the synonym of Real but if you use Float you avoid the cast to Real. Hope this helps.|||

Did you get a solution? I have the same issue.... Please let me know at the earliest...

import csv files into MS SQL Server 2K

Hi There,

I have a requirement to import a large number of csv files to one table. The files are in the format NAME.date.csv. What is the best way to do this?

Cheers

Pete

There are several options, bcp, dts or bulk insert.

Depending on how much logic should be applied in the import you may want to use dts or bulk insert.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

import CSV file?

how can i import CSV data to sql server using sql statement?
thanksSee the documentation for BULK INSERT

http://msdn2.microsoft.com/en-us/library/ms188365.aspx
|||it is difficult to create XML file, how i can create xml for CSV file from table that i will have to import into?
thanks

Import CSV File with FTP as Source

I want to use a FTP Task to obtain a file on a remote server, then
transfer into a table.
I'm sure it can be done, but there aren't many tutorials explaining
how it works only thin acknowledgments.Hi
"JGiotta" wrote:
> I want to use a FTP Task to obtain a file on a remote server, then
> transfer into a table.
> I'm sure it can be done, but there aren't many tutorials explaining
> how it works only thin acknowledgments.
>
You can use DTS for SQL 2000 or SSIS for ASQL 2005 expecially if there are
multiple files. For DTS check out http://www.sqldts.com/302.aspx and
http://www.sqldts.com/246.aspx
You will need to do this in two stages, get the FTP files and then import
them.
John

Import CSV File with FTP as Source

I want to use a FTP Task to obtain a file on a remote server, then
transfer into a table.
I'm sure it can be done, but there aren't many tutorials explaining
how it works only thin acknowledgments.Hi
"JGiotta" wrote:

> I want to use a FTP Task to obtain a file on a remote server, then
> transfer into a table.
> I'm sure it can be done, but there aren't many tutorials explaining
> how it works only thin acknowledgments.
>
You can use DTS for SQL 2000 or SSIS for ASQL 2005 expecially if there are
multiple files. For DTS check out http://www.sqldts.com/302.aspx and
http://www.sqldts.com/246.aspx
You will need to do this in two stages, get the FTP files and then import
them.
John