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:
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!
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:
Code:
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!