New to SQL - Need help importing TXT files

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

General SQL Server Forums

New to SQL Server Programming

New to SQL - Need help importing TXT files

Author

Topic

jcraneSQL
Starting Member

4 Posts

Posted - 2009-06-23 : 14:49:59

Here's my scenario: SQL Server Management Studio 2008

Let's say I have the following files in the Z:\Import directory:
1234.txt
2345.txt
3456.txt

Each of the above files contains text that needs to be imported to a specific database/table/row/column.

I have a SQL table setup as follows:

I need to simultaneously import all of the text file contents into the above table where the DocumentID matches the corresponding filename.

Any ideas how to do this? Would I need to create some sort of CSV file with the DocumentID in one column and the TXT file location in another column and then somehow import that into SQL? Any help would be appreciated. I'm in a little over my head! Thanks in advance...

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-23 : 16:17:02

I am completely new too, but I am in the same boat as you regarding importing data.
To import data, I might be able to help you, but it's not perfect, because I cannot get the commands saved. Meaning, that I save the command syntax in a .txt file and copy/paste the commands into New Query, whenever I want to import my file.

First I create a table using the following kind of syntax (the article that explains this can be found at http://blogs.techrepublic.com.com/datacenter/?p=317&tag=rbxccnbtr1)
CREATE TABLE [dbo].[SalesHistory]
(
[SaleID] [int],
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
So if I was you I would make a 'working' table called something like ImportedFiles with columns such as [DocumentID], [Status], [Pages] and [Contents].
In the properties, I would set "is identity" to "Yes", Identity Seed = 1234 and Identity increment=0. I have not tested this but I think it will give you an autonumber = 1234 for every line. Solving your DocumentID portion.

Use something like
BULK INSERT SalesHistory FROM 'c:\SalesHistoryText.txt' WITH (FIELDTERMINATOR = ',') (also typed into NEw Query as explained)

Once your 'working' table is populated then you can do inserts into your main table. I cannot explain this yet because that's where I am researching at the moment. If I find solution or better way, I shall let you know.

jcraneSQL
Starting Member

4 Posts

Posted - 2009-06-23 : 16:24:13

Thanks for the info. I'll look into what you've suggested. The only thing is that the DocumentID, Status, Pages, and Contents values already exist. I ONLY want to update the Contents field with the content of the txt file who's filename matches the existing DocumentID. I am not trying to simply populate an existing table. Hope this makes sense.

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-23 : 16:45:29

This is why I suggested that you import your data into a working (temporary) table.

Then you update from that table to your proper table, only the columns that need updating.

So you will need a View, that SELECTs records FROM working table WHERE they are equal to proper table .. and then inserts these records.

I do not think I can be of more help than that.

jcraneSQL
Starting Member

4 Posts

Posted - 2009-06-24 : 09:32:50

Oh okay. I think I misread your first post. Sorry for the confusion and thanks again for the help. I'll be working on this all day!

jcraneSQL
Starting Member

4 Posts

Posted - 2009-06-24 : 11:35:06

From what I've read, I don't think these suggestions will perform what I need to do. I need to get the physical text from the TXT files I have into the 'Contents' column of my table. The TXT files I have are actual documents and the text itself needs to be in the 'Contents' column. Each of the TXT filenames match an existing DocumentID in my table. I need to find a way to import then entire contents of the TXT files to the 'Contents' column where the TXT filename matches the existing DocumentID. I'll be working with thousands of TXT files, so I need to find a way to import multiple files simultaneously. I see where you're going with the temporary table, but I still don't know how to get the text into the temporary table. Thanks again for your help, it has been very useful so far.

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-24 : 20:28:56

The following works for me. But this one imports from an .xml file.

I have a table with 2 column
FileName, data

then ..


DECLARE @FileName VARCHAR(300)
SELECT  @FileName = 'C:\Test.xml'
-- dynamic sql is just so we can use @FileName variable in OPENROWSET
EXEC('
INSERT INTO Import(FileName, data)

SELECT ''' + @FileName + ''', Data 
FROM
(
    SELECT  * 
    FROM    OPENROWSET (BULK ''' + @FileName + ''' , SINGLE_BLOB) AS DATA
) AS FileImport (DATA)
')

hope it works

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources