Click here to Skip to main content
15,567,466 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I want to update MSSQL Server pre-existing Table with the data on CSV file but because the CSV files are generated with random names so i want to sequentially open each file as they are created and update table in my database and then delete this file once the record is added.
Posted

1 solution

Configure importing from a constant file and use the batch file like this:
SQL
@echo off

for %%i in (*.csv) do call :RUN_ONE %%i
goto END

:RUN_ONE

ren %%i import.csv

rem Load import.csv into SQL Server.

del import.csv

:END


For example, you may create a stored procedure that uses OPENROWSET to select data from a file:
SQL
INSERT INTO ... ( ... )
SELECT
    ...
FROM OPENROWSET(BULK '...import.csv',
    FORMATFILE = '...import.fmt', FIRSTROW = 2)


and use sqlcmd to execute the procedure. For example:

sqlcmd -S . -E -h -1 -d Database -Q "EXEC dbo.uspImport"
 
Share this answer
 
v3
Comments
Member 10847288 27-May-14 11:44am    
Thanks mate for your prompt reply but i need vb code for this as i am not very much use to MSSQL queries.
Sergey Vaselenko 27-May-14 11:52am    
What technique do you use to import data?
Take a look at similar techniques for importing from Excel
http://www.excel-sql-server.com/excel-import-to-sql-server.htm
Member 10847288 27-May-14 11:53am    
yes CSV file contains the data to be added into the MSSQL table. Files are generated in same folder with random names.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900