Importing Selected Rows into database

Question

Importing Selected Rows into database

NoviceJatt

SSCommitted

Points: 1671
More actions
July 8, 2010 at 12:19 pm

#223067

Hi,
I'm new to SQL so I need help with a task.
I have few text files that I want to import into the database. I'm using import/export wizard to do this. But while importing, I need to skip certain rows like 'subtotal' and 'total'. These rows occur every 10 or 12 rows. Also I want to create a procedure so that everytime I drop a text file into the folder, SQL should automatically load the data into same database. I want to use this data for reporting purpose. So what I need is an automated procedure to import selected rows and columns into database. Can you guys give me any ideas?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Tara Kizer SSCommitted Points: 1789 More actions · Answer 1

You should import your data into a staging table, and then move your data from the staging table to your actual table via T-SQL. With T-SQL, we can easily add a WHERE clause to your INSERT/SELECT statement to exclude/skip certain rows.

You can wrap this into an SSIS package. You can schedule it to run each day. Will the file name change each day?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog

NoviceJatt SSCommitted Points: 1671 More actions · Answer 2

Yes, thats what I'm trying to do. I want to create a SSIS package for this. And yes, the file name will change daily. We use to cut, copy, paste different columns from different files into one file in excel and now we want to use SQL to do this task automatically. So it would be a great help if you could give me anyother ideas.

Thanks Tara

Tara Kizer SSCommitted Points: 1789 More actions · Answer 3

Try this: http://www.mssqltips.com/tip.asp?tip=1395

Or these: http://www.google.com/search?hl=en&rlz=1C1GPMD_enUS313US313&q=ssis+import+data+dynamic+file+name&aq=f&aqi=&aql=&oq=&gs_rfai=

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog

sturner SSC-Insane Points: 22411 More actions · Answer 4

I would write a little .NET program to do this... grab the file from the folder, pre-format/filter it as needed then bulk insert it into a database table or tables. .NET has a System.IO.FileSystemWatcher class that is very handy and powerful for monitoring a folder for files etc.

SSIS is okay for straightforward jobs that are simple and repetitive. If I want something more robust and flexible then a .NET app is the way to go. just my $.02

The probability of survival is inversely proportional to the angle of arrival.

NoviceJatt SSCommitted Points: 1671 More actions · Answer 5

Since I don't have much knowledge of .NET so can you provide me with the code so that I can modify it according to my needs.

Thanks

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 6

If you have a process now and want to automate it, you'll need to learn to program a bit. I wouldn't do this in .NET, but rather use SSIS. Some .NET is needed, but what you want to do is break down your process into the steps you perform, then automate each of those.

Think about what you do, how you'd explain that to someone, and write it down. Then search out code to help you do those parts. Tara has given you some links, and if you have specific questions on each part, we can help with those.

To pull in a file, you need the FileWatcher task. (Google for that)

If you pull in all the data into a staging table, you can then run a stored procedure that removes the total rows for you before moving the data into another table.

To give you more code, you'd have to post more details.

NoviceJatt SSCommitted Points: 1671 More actions · Answer 7

Thank you very much guys. I'll try doing it by this method.

I have one more question. As I told you that I'm going to use this data for Reporting purpose. So if I only use SQL to do this task, will I have that much control over the data ? Considering that I need to automate data import from a folder containing csv and txt files. Can I schedule a SQL query to do all these tasks ?

Thanks

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 8

SSIS can handle the import. The T-SQL part would deal with data once it was inside SQL Server. Basically

1. SSIS has a loop/file watcher to grab files and import them, maybe transform. Load to staging tables inside SQL Server

2. T-SQL to clean out rows from your file that isn't done in SSIS. This can be part of a job, or a task in an SSIS package.

3. T-SQL to move data from staging to real tables.

I tend to like using T-SQL since I think it's simpler (for me) to move data around and do simple manipulation like removing rows that have specific values in them. However if you are good at scripting or using some of the SSIS tasks, you can do it there.

NoviceJatt SSCommitted Points: 1671 More actions · Answer 9

NoviceJatt

SSCommitted

Points: 1671

July 8, 2010 at 3:31 pm

#1190526

Thank you very much.

NoviceJatt SSCommitted Points: 1671 More actions · Answer 10

Hi

Here is what I want. I have attached two excel files(Test.xls and Test2.xls) with this.

Test.xls is the file that I want to import and Test2.xls is how I want to import that into a table by using T-SQL. Two columns and two rows are skipped. The time column has date, but does not show up in excel, but it shows up if I save this file as a text file. I was trying BULK INSERT to do the job. Can you give me sample code for this? Or any other ways to do this.

Thanks