pulling data from Flat file to a table

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.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

pulling data from Flat file to a table

Author

Topic

freestyle
Starting Member

5 Posts

Posted - 2007-11-02 : 23:09:27

I need to do some advanced formatting in the text file, because the data in the text file is bit complex. I have some knowledge of using BCP, BULK INSERT and bit about FORMAT FILES but I want to do some advanced formatting(using IF condition and all) to pull the data from text file.
Can anyone please tell me how can I perform bit advanced formatting using BULK INSERT?

Cheers

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-02 : 23:39:45

You can load data to staging table then manipulate them with t-sql.

freestyle
Starting Member

5 Posts

Posted - 2007-11-03 : 07:39:49

how do I load the data to staging table? Could you please show me an example please?

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-03 : 20:27:39

Use bulk insert or bcp or ssis or data import wizard, you can find samples in books online.

sanjnep
Posting Yak Master

191 Posts

Posted - 2007-11-03 : 20:38:28

You can't put complex logic on bulk insert just it is the fastet way to load data in SQL Server.In my experence it is better you put your logic(like if else,trim) using VB script using file system objcet or some thing like that and put it another text file. I mean read line by line on unformatted file - use vb script or vb.net script to format the data - put it new file. Only after than you can bulk insert this formatted file to SQL Server.
I also tried loading unformatted file and latter using t-sql to format the data but it was slower than previous option. i couldn't figure out the cause. There was about 8 million data on the text file.

Thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-03 : 20:54:39

quote:
Originally posted by freestyle

how do I load the data to staging table? Could you please show me an example please?

Thanks

Can't really show you but you would import into an unformatted columnar table (let's say import_MyTable)

You would then use T-SQL to format the columns as necessary into your final table

I would presume you could also use an active x script for column manipulation or formatting.

freestyle
Starting Member

5 Posts

Posted - 2007-11-04 : 13:52:01

Thanks all for responding

As you all suggested, I have planned to format file and write to a another text file using a programming language and then pull the data to a table.
Really appreciated

Kristen
Test

22859 Posts

Posted - 2007-11-04 : 14:36:24

Although it may depend on what "formatting" you are doing, I find this easier in a staging table, rather than in a "sequential" language.

In the old days I used a sequential language and did:

Process file
Import file
Look at data with application
Discover additional "data manipulations" that were needed
Repeat

Now I do:

Import file into Staging table
Run script of manipulations
Look at data with application
Add additional step to script
Run additional step (only)
Re-examine with application

Plus I can do

SELECT COUNT(*) FROM MyTable WHERE ... SomeCondition ...
and
SELECT TOP 50 * FROM MyTable WHERE ... SomeCondition ...

to locate candidate data for UPDATing

and


BEGIN TRANSACTION

UPDATE U
SET SomeColumn = SomeManipulation
FROM MyTable AS U
WHERE SomeCondition

SELECT TOP 50 * FROM MyTable WHERE ... SomeOtherCondition ...

-- COMMIT or ROLLBACK if it doesn't work / changes too many rows / etc

Kristen

freestyle
Starting Member

5 Posts

Posted - 2007-11-05 : 04:32:55

Kristen,

yeah I also think that is the right way to do it.
But I dont actually understand what is Staging table means. What do you realy mean by that? is it just a temporary table or wat?

Thanks alot

Kristen
Test

22859 Posts

Posted - 2007-11-05 : 05:03:20

"is it just a temporary table"

Yup, basically. Its usually a table with "little" data typing. All the numeric columns might be VARCHAR(50) or somesuch. That way if any columns are not ACTUALLY numeric you don't get an error importing them.

Then you can query each numeric column for any non-numeric values, and decide what you are doing to do about them. Maybe take out the "," between the thousands, whatever.

The staging table might be "permanent", but not part of the application, will have no Foreign Keys, and may not even have a Primary Key [if you can not be certain that the PK column(s) will be unique, for example]

And once you've cleaned up the staging table you can insert from there into your "real" table.

We usually place our Staging Tables in a separate database - so that the Production Database is not "polluted" by all the batch processing of the Staging Tables; and we also use Simple Recovery Model for the Staging Database (whereas our Production databases use Full Recovery Model)

We use Suffixes for our database names:

xxx_LIVE - Production
xxx_TEST - QA
xxx_XFER - Transfer/Staging

so a given "project" name have 3 databases with the same name, but different suffixes

Kristen

freestyle
Starting Member

5 Posts

Posted - 2007-11-05 : 10:59:20

nw i got a fair idea about it...
cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources