1

I am quite new with SQL Server. I have got a .txt file with values separated like this:

20120101;001;2;0;0;0;0;0;8
20120102;002;3;0;0;0;0;0;8
20120103;003;4;0;0;0;0;0;8
...

This file is in a server and I need to import this file everyday at the same hour autommaticaly, and update the changes (the file is changing every day). I was thinking in making a function to do it but I don't know if it is even possible.

Any help would be appreciated. Thanks in advance.

andoni90
  • 1,066
  • 2
  • 12
  • 30

2 Answers2

3
insert into your_table
  SELECT a.*  
  FROM OPENROWSET( BULK 'c:\input.csv', FORMATFILE = 'c:\Format.fmt') AS a;

Exaample Format.fmt

9.0
6
1       SQLCHAR     0     15   ";"      1     col1      SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR     0     25   ";"      2     col2      SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR     0     6    ";"      3     col3      SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR     0     5    ";"      4     col4      SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR     0     15   ";"      5     col5      SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR     0     100  "\r\n"   6     col6      SQL_Latin1_General_CP1_CI_AS
juergen d
  • 201,996
  • 37
  • 293
  • 362
2

You could use SQL Server Integration Services (SSIS) to import flat files on a scheduled basis.

Here is an example that loops through all CSV files of same format in a given a folder and loads them into database.

How do I move files to an archive folder after the files have been processed?

Here is another example:

Validate CSV file Data before import into SQL Server table in SSIS?

Following answer shows how to schedule an SSIS package to run from inside a SQL Server Agent job.

How do I create a step in my SQL Server Agent Job which will run my SSIS package?

Hope that gives you an idea.

Community
  • 1
  • 1