Importing flat file, truncation and converting issues

[sschrupp]

Hi, we're in the process of moving from a bunch of Access databases to a SQL Server environment. I have no experience with SQL Server (we use a lot of Excel, Access, and VBA here), but I'm managing to figure things out so far except for one issue.

I've built the tables and made some queries just fine. Where I'm encountering problems is when I try to import data from some text files. Trying different ways leads to different issues with truncation or conversion error messages.

First off, when using the Import Wizard the file is imported just fine. But since I'm temporarily using the Express version I can't save that process, only run it now and forget it. When I attempt to use BULK INSERT in queries I always get either truncation or conversion problems.

I even figured out how to use BCP to create a format file and have the query refer to that without success. Here is the query I've tried:

BULK INSERT [Active Accounts] 
FROM '\\Blah\Blah\Data\Active Accounts.txt'
WITH 
    ( 
	FIRSTROW = 2,
        FIELDTERMINATOR = '\t', 
        ROWTERMINATOR = '\n',
        FORMATFILE = 'C:\Import.fmt'
    )

If I add "MAXERRORS = 1000000" it will import about half my data and skip the rest, whereas using the Import Wizard it imports it all flawlessly.

I also tried importing to a temporary table first which has all varchar(50) fields, and then inserting the data from that table to the table I want, but then I get a "Error converting data type varchar to numeric." I would have assumed using SQL Server it would have been smart enough to automatically convert fields like Access does.

So what SQL statements do I need to do in order to mimic what the Import Wizard does perfectly?

Eventually I hope to have an easy button created that the user can push that will import a few text files, populate some tables, play with the data, etc. Not much of an easy button if I can't even get it to import the text files I need.. hehe.

Thanks for any advice!

 

[djj55]

SQL does not import or export easily outside of the wizards and SSIS (DTS).
SQL will automatically convert some things but not all. There are two commands CAST and CONVERT that are used to change the type of a field.

One way to automate would be to create the table on SQL then use Access as the front end (forgive me for saying that). You can use an ODBC connection and insert using Access.


To your problem. I would suspect that there is bad data in a column such as mixed type.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[sschrupp]

Ahh, ok. Thanks for the tip.

So when we go to the full version of SQL Server, instead of playing around with Express, and I have access to SSIS you think that I will be able to do what the wizard does? I don't doubt that there's some goofy data in the flat files. They're dumped from Business Objects to tab delimited text files.

I'm assuming though if the Wizard is able to do it there must be a way to do it without the Wizard. I just found it slightly amusing that I can easily do all this in Access without too many bumps in the road (well, besides us battling the 2 GB limit on 6 different Access DBs thus the move) and then I try a powerful tool only to hit a brick wall. I figured the brick wall was my ignorance and not SQL Server though.

And yeah, we're trying to avoid Access in favor of me learning something new which is fine by me.

 

[gmmastros]

A good friend of mine blogged on different ways to import data in to SQL Server.

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/title-12

I suggest you read through this to see if anything there helps. It is common to import in to a staging table, then do some cleanup work, and finally copy from the staging table to your real tables. If you are running in to a 2 GB limit in Access, it probably won't take long to run in to the 4 gigabyte limit of sql express.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sschrupp]

Wow, thanks George. That'll come in very handy once I can get my grubby paws on a real version instead of Express.

 

[djj55]

Thanks George that is a good link. (I noticed you helped out).

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

I noticed you helped out

I've been known to do that from time to time. Actually, though... I didn't really do all that much here.

I often refer to this blog because it shows an example of a format file for importing a comma delimited file with quote text qualifiers.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

If you are using the wizard, you can change the column length for the flat file and that will fix your truncation problems. The wizard now assumes a length of 50 which is ridiculously low for any file I've ever had to import this way. In the wizard when setting up the flat file source, go to the advanced tab and change the sizes of the columns. I find it works best to import these kinds of files to a staging table. Then use t-sql to clean and move the data to the production table. That should fix your conversion problesm as you can write code to work around them.

"NOTHING is more important in a database than integrity." ESquared