How to easily import a .csv file into an existing table in SSMS?

%3CLINGO-SUB%20id%3D%22lingo-sub-1643482%22%20slang%3D%22en-US%22%3EHow%20to%20easily%20import%20a%20.csv%20file%20into%20an%20existing%20table%20in%20SSMS%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1643482%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20a%20bunch%20of%20.csv%20files%20(one%20for%20each%20day%20of%20logs)%20that%20I%20want%20to%20import%20into%20a%20table.%3C%2FP%3E%3CP%3EMy%20issue%20is%20that%20I%20am%20having%20a%20very%20hard%20time%20with%20creating%20the%20table%20in%20the%20first%20place.%3C%2FP%3E%3CP%3EHalf%20of%20the%20content%20of%20the%20.csv%20files%20is%20in%20Japanese%2C%20this%20might%20be%20related%20to%20the%20problem.%20When%20importing%20a%20.csv%20file%20using%20both%20the%20wizards%20in%20the%20program%2C%20the%20automatic%20data%20type%20detection%20does%20not%20work.%20I%20had%20to%20work%20around%20it%20by%20making%20a%20table%20adding%20everything%20as%20text%20and%20using%20a%20query%20to%20get%20the%20max%20length%20of%20the%20all%20the%20columns%20and%20apply%20it%20to%20creating%20a%20new%20table.%20Next%2C%20I%20also%20have%20problems%20with%20things%20such%20as%20cannot%20convert%20string%20to%20type%20nvarchar%2C%20etc.%20The%20thing%20that%20bugs%20me%20with%20this%20is%20I%20have%20a%20lot%20of%20columns%20and%20if%20I%20try%20and%20it%20doesn't%20work%2C%20it%20tends%20to%20reset%20some%20times%20and%20I%20have%20to%20re-do%20everything%2C%20taking%20ridiculously%20long.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I%20have%20made%20the%20table%20for%20one%20file%20before.%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20was%20thinking%20the%20easiest%20way%20would%20simply%20be%20to%20import%20all%20the%20csv%20files%20directly%20into%20the%20table%20I%20already%20made.%20Using%20the%20wizard%2C%20however%2C%20I%20get%20the%20below%20problem%3A%3C%2FP%3E%3CP%3EI%20open%20the%20Import%20Data%20wizard%2C%20and%20select%20my%20source%20flat%20file.%20It%20displays%20correctly%20in%20the%20preview.%3C%2FP%3E%3CP%3EI%20select%20my%20destination%20table%2C%20the%20table%20I%20already%20made%20before%20that%20is%20working%20with%20correct%20data%20types.%3C%2FP%3E%3CP%3EEvery%20item%20is%20green%20except%20for%20one%20yellow%20triangle%20at%20a%20column%20called%20customer%20acc%20number.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20run%20it%2C%20it%20fails%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMessages%3CBR%20%2F%3EError%200xc02020a1%3A%20Data%20Flow%20Task%201%3A%20Data%20conversion%20failed.%20The%20data%20conversion%20for%20column%20%22%20Hold_at%22%20returned%20status%20value%202%20and%20status%20text%20%22The%20value%20could%20not%20be%20converted%20because%20of%20a%20potential%20loss%20of%20data.%22.%3CBR%20%2F%3E(SQL%20Server%20Import%20and%20Export%20Wizard)%3CBR%20%2F%3E%3CBR%20%2F%3EError%200xc0209029%3A%20Data%20Flow%20Task%201%3A%20SSIS%20Error%20Code%20DTS_E_INDUCEDTRANSFORMFAILUREONERROR.%20The%20%22Source%20-%200618_csv.Outputs%5BFlat%20File%20Source%20Output%5D.Columns%5B%20Hold_at%5D%22%20failed%20because%20error%20code%200xC0209084%20occurred%2C%20and%20the%20error%20row%20disposition%20on%20%22Source%20-%200618_csv.Outputs%5BFlat%20File%20Source%20Output%5D.Columns%5B%20Hold_at%5D%22%20specifies%20failure%20on%20error.%20An%20error%20occurred%20on%20the%20specified%20object%20of%20the%20specified%20component.%20There%20may%20be%20error%20messages%20posted%20before%20this%20with%20more%20information%20about%20the%20failure.%3CBR%20%2F%3E(SQL%20Server%20Import%20and%20Export%20Wizard)%3CBR%20%2F%3E%3CBR%20%2F%3EError%200xc0202092%3A%20Data%20Flow%20Task%201%3A%20An%20error%20occurred%20while%20processing%20file%20%22C%3A%5CUsers%5Cxxxxx%5CDesktop%5CLog%5CTODO%5C0618.csv%22%20on%20data%20row%202.%3CBR%20%2F%3E(SQL%20Server%20Import%20and%20Export%20Wizard)%3CBR%20%2F%3E%3CBR%20%2F%3EError%200xc0047038%3A%20Data%20Flow%20Task%201%3A%20SSIS%20Error%20Code%20DTS_E_PRIMEOUTPUTFAILED.%20The%20PrimeOutput%20method%20on%20Source%20-%200618_csv%20returned%20error%20code%200xC0202092.%20The%20component%20returned%20a%20failure%20code%20when%20the%20pipeline%20engine%20called%20PrimeOutput().%20The%20meaning%20of%20the%20failure%20code%20is%20defined%20by%20the%20component%2C%20but%20the%20error%20is%20fatal%20and%20the%20pipeline%20stopped%20executing.%20There%20may%20be%20error%20messages%20posted%20before%20this%20with%20more%20information%20about%20the%20failure.%3CBR%20%2F%3E(SQL%20Server%20Import%20and%20Export%20Wizard)%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20know%20this%20might%20be%20a%20hard%20case%20to%20solve%20without%20a%20lot%20of%20additional%20information%2C%20so%20I'd%20rather%20ask%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20no%20simple%20way%20to%20just%20import%20a%20set%20of%20data%20into%20a%20table%20with%20the%20same%20columns%3F%3C%2FP%3E%3CP%3ESay%2C%20I%20use%20the%20data%20for%20May%201st%20as%20a%20template%20and%20make%20a%20May%201st%20table.%3C%2FP%3E%3CP%3EI%20rename%20May%201st%20table%20to%20'tbLogs'.%3C%2FP%3E%3CP%3EFrom%20May%202nd%20and%20onwards%2C%20I%20just%20import%20the%20data%20into%20tbLogs%20instead%20of%20creating%20new%20tables.%3C%2FP%3E%3CP%3EThe%20data%20types%20should%20all%20be%20the%20same%20and%20it%20should%20go%20hand%20in%20hand%20just%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20the%20above%20not%20possible%3F%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi, I have a bunch of .csv files (one for each day of logs) that I want to import into a table.

My issue is that I am having a very hard time with creating the table in the first place.

Half of the content of the .csv files is in Japanese, this might be related to the problem. When importing a .csv file using both the wizards in the program, the automatic data type detection does not work. I had to work around it by making a table adding everything as text and using a query to get the max length of the all the columns and apply it to creating a new table. Next, I also have problems with things such as cannot convert string to type nvarchar, etc. The thing that bugs me with this is I have a lot of columns and if I try and it doesn't work, it tends to reset some times and I have to re-do everything, taking ridiculously long.

 

However, I have made the table for one file before. 

So I was thinking the easiest way would simply be to import all the csv files directly into the table I already made. Using the wizard, however, I get the below problem:

I open the Import Data wizard, and select my source flat file. It displays correctly in the preview.

I select my destination table, the table I already made before that is working with correct data types.

Every item is green except for one yellow triangle at a column called customer acc number. 

I run it, it fails:

 

Messages
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column " Hold_at" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Source - 0618_csv.Outputs[Flat File Source Output].Columns[ Hold_at]" failed because error code 0xC0209084 occurred, and the error row disposition on "Source - 0618_csv.Outputs[Flat File Source Output].Columns[ Hold_at]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\Users\xxxxx\Desktop\Log\TODO\0618.csv" on data row 2.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - 0618_csv returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

I know this might be a hard case to solve without a lot of additional information, so I'd rather ask this:

 

Is there no simple way to just import a set of data into a table with the same columns?

Say, I use the data for May 1st as a template and make a May 1st table.

I rename May 1st table to 'tbLogs'.

From May 2nd and onwards, I just import the data into tbLogs instead of creating new tables.

The data types should all be the same and it should go hand in hand just fine.

 

Is the above not possible?

Thank you!

0 Replies