Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Fireman Sam
Starting Member
2 Posts |
 Posted - 2009-01-28 : 05:05:33
|
Hi Guys,First post for a new member and a problem that I have been having in trying to automate a procedure in SQL rather than my longwinded makeshift way at the moment. We get some data down in CSV format which is semi colon delimited. The file comes down each day as filenameyyyymmdd.txt and its a biggy though I only need about 8 columns of data out of about 103 columns from it. I am newish to SQL but have a fair amount of experience in Excel and Access and so the way I am doing it at the moment is importing just the columns I need in Excel (just have to change the date each day for the filename when I do a refresh) as it has too many columns to pull directly into access. I then have a link from access to this file which also has a link to a table on the SQL server. I delete the data from the SQL table and re-import the data from the excel file through access to the SQL server using an access query. All long winded and a pain as I have to do this manually. Is there a way that I can automatically import data (just the fields I need) to SQL which will refresh with the daily flat file which has a changing date filename on a daily basis. All our data is one day behind so we get Friday on Monday but the other days are one day behind. The flat files are collected in one directory, so even if it can pick the last file from that directory (the one with the latest save date) but other files with different names are also in here on a daily basis. ie todays file is called TJC540E20090127.txt, tomorrows will be TJC540E20090128.txt and this time next month will be TJC540E20090227.txt.====================================When I grow up I want to be an SQL Programmer |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-01-28 : 06:30:32
|
| Do a forum search on CSV Import, there are loads of posts on here that will help you. |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-01-28 : 10:44:44
|
It's not that hard really...What you need to do is to create a procedure that will find the filename (I assume that the file is in some way visible from the sql server) and then create a BULK INSERT. Finding the filename can be done like this:DECLARE @dirCmd varchar(200), @FileName varchar(200)DECLARE @Files table ( ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, [Filename] VARCHAR(500))SET @dirCmd = 'dir /b d:\myfiles\*.txt" 'INSERT INTO @Files ([Filename])EXEC xp_cmdshell @dirCmdSELECT TOP 1 @FileName = [Filename] FROM @Files ORDER BY RIGHT([Filename], 12) DESCSELECT @FileName And then you should look up BULK INSERT in books online to create the insert statement...- Lumbago |
|
|
|
Bodestone
Starting Member
18 Posts |
Posted - 2009-02-06 : 19:32:40
|
| Alternatively, if this is going to be run as an automated task after the new file has been added and there are no problems with near midnight timings:SET @filename = 'basefilename' + left(replace(convert(varchar, getdate(), 120),'-',''),8) + '.txt'That way bulk insert should throw an error if the new file has not been put in place rather than just re-inserting the last one. |
|
|
|
Fireman Sam
Starting Member
2 Posts |
Posted - 2009-02-18 : 05:36:34
|
quote:
Originally posted by RickD
Do a forum search on CSV Import, there are loads of posts on here that will help you.
Thanks for the help guys, finding the correct file was easy with a little tweak of the script Lumbago posted, though I am sure that the one bodestone posted would work equally well for the task at hand.I have done a few weeks searching on the import of the CSV file as recommended, but I can't find the information I am looking for, or maybe because I don't know the way of doing it I can't filter the amassed amount of data to what I think I need.My CSV file is in the format"Field 1 text ";"Field 2 text ";"Field 3 currency ";"Field 4 int ";"etc ect for roughly 190 fieldsI need to automate importing this file and overwriting the previous data in a table on a daily basis but I only need to pull in, for instance, fields 2,6,7,9,11,61,82 and ignore the rest. From the information I looked at, there was a way to pull in all the data using bulk insert, but I don't want to pull all the fields in. Is there a way to do this?If it is any help it seems that the file as well as being ";" delimited is also fixed length as it is pretty neat when I look at it in TextPad.I would also need to ignore the first and last record as the first contains the date of the file and the last contains the number of records processed.====================================When I grow up I want to be an SQL Programmer |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-02-18 : 05:44:35
|
| What you can do is to create a staging table with all the columns that you have in the file, bulk insert in to that table, and then when you want to import to the "real" table just do a normal select of the columns you want from the staging table. Quite simple and if you need more/less columns from the file at a later time it will be really easy to just change the select statement...- Lumbago |
|
|
|
|
|
|
|
|
Import of CSV File with changing filename