asked
inserting a .csv file into a database table
Hey, The code below is supposed to insert a .csv file into a database table but it is only creating the table and not posting the records. Kindly help me troubleshoot the code. Thank you. USE [TEST] GO drop table #t_Statement CREATE TABLE #t_Statement ( [TRANS_DATE][nvarchar](1000) NULL , [TRANS_DETAILS][nvarchar](1000) NULL, [TRANS_MONEY_OUT] [nvarchar](1000) NULL, [TRANS_MONEY_IN] [nvarchar](1000) NULL, [TRANS_LEDGER_BAL] [nvarchar](1000) NULL, [TRANS_AVAILABLE_BAL] [nvarchar](1000) NULL) --PRIMARY KEY [TRANS_DETAILS]) BULK INSERT #t_Statement FROM 'C:\StatementScripts\Statement.csv' WITH ( FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n', FIRSTROW = 2 )
Do you get any error? Check your delimiter first. How many rows do you have in your csv file? If you have only one row, your script start importing from row 2 (FIRSTROW = 2). Also, check folder security, do you have access rights? For security options read this
https://msdn.microsoft.com/en-us/library/ms188365.aspx
The code does not return any error, just an empty table. i have 24 rows and yes i have access rights
For test, create table t_Statement_Test in your database. Using import wizard (right click on database -> Tasks -> Import data), for data source choose flat file and choose your csv file, for data destination choose t_Statement_Test table. For data delimiter use Tab and try to import data. Is it works for you?
Try one more time with BULK INSERT, but use ROWTERMINATOR = '0x0a' or ROWTERMINATOR = '\r\n' instead of ROWTERMINATOR = '\n' And add one more option: DATAFILETYPE = 'char'
- Executing (Error) Messages Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Column 0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard) I got this error after using the import wizard Error 0xc020902a: Data Flow Task 1: The "output column "Column 0" (10)" failed because truncation occurred, and the truncation row disposition on "output column "Column 0" (10)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard) Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\BankStatementScripts\KcbStatement.csv" on data row 1. (SQL Server Import and Export Wizard)
5 People are following this question.
