Need Help - Importing Text 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

Need Help - Importing Text Files

Author

Topic

nickjb16
Starting Member

6 Posts

Posted - 2009-07-14 : 10:25:05

Hi,

I need to import log files in txt format on a daily basis.
Details:
- Log files dumped to a network share.
- DB has to be updated daily without duplicates.
- Running SQL Server 2005

Text File Format:
Company Computer Asset Information Log File
Information valid as of Mon 07/06/2009
System Name="AABBCCDD-XP";
Serial Number="4325454";
Machine Model="2234325";
System Type="ThinkPad T400";
Hardware Type="Notebook";
Wired MAC Address="00:00:00:00:00:00";
Wireless MAC Address="00:00:00:00:00:00";
BIOS Version="7UET66WW (2.16 )";
Warranty Information="2012-05-25";
Region="Asia Pacific";
Location="Noida";
Organizational Unit="Engineering";
Engineering="Yes";
Productivity="No";
Serviced Business Desktop (SBD)="No";
Full Name="John Q Frustrated";
Personal Login="jfrustrated";
Image Build Version="Version 2.2.9";
Image Deploy Date="Mon 07/06/2009";
Company Asset Number="NA";
Technician="ajskdps";
DeployDuration="109";
BIOS Update="Yes";
Repository="imageserver.cs.corp.com";

Any ideas?

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-07-14 : 11:36:12

To easily get this into SQL, use SSIS and import it into an empty table (or BCP). Follow the wizard to get table formatting correct. Once that's on your DB server, come up with your update/insert script. If you need additional help, post back.

Terry

-- Procrastinate now!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-14 : 13:01:03

In ssis:-
1.use ftp task for storing files in n/w share
2.use data flow task to insert/update table from files

nickjb16
Starting Member

6 Posts

Posted - 2009-07-14 : 13:46:12

I can't seem to get an FTP connection configured to transfer the contents of an entire directory.

Also, when using the import wizard, I can parse the file into columns but cannot separate the title from the value - ie. System Name="AABBCCDD-XP"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-14 : 13:53:46

quote:
Originally posted by nickjb16

I can't seem to get an FTP connection configured to transfer the contents of an entire directory.

Also, when using the import wizard, I can parse the file into columns but cannot separate the title from the value - ie. System Name="AABBCCDD-XP"

use a derived column task and apply substring over System Name variable to get part you want.
where do you want to transfer files from?

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-07-14 : 13:55:46

FTP is a sys admin issue. As far as separating title from the value, what's the titl and waht's the value? Based on what you've posted i.e. System Name="AABBCCDD-XP", it doesn't tell us a whole lot. Is the value "XP"? 'AABBCCDD"? all of the above? none of the above?

Terry

-- Procrastinate now!

nickjb16
Starting Member

6 Posts

Posted - 2009-07-14 : 13:56:45

They are from another network share. Its basically a folder that is updated daily with all the recent log files in txt format.

nickjb16
Starting Member

6 Posts

Posted - 2009-07-14 : 13:57:44

The column title would be System Name and the value would be AABBCCDD-XP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-14 : 14:37:49

quote:
Originally posted by nickjb16

They are from another network share. Its basically a folder that is updated daily with all the recent log files in txt format.

then isnt possible to use For each loop with file enumerator and point it to network share so that it processes over files in folder.

nickjb16
Starting Member

6 Posts

Posted - 2009-07-21 : 11:24:02

Would you have an example of how to configure derived column task with substring?

quote:
Originally posted by visakh16

quote:
Originally posted by nickjb16

I can't seem to get an FTP connection configured to transfer the contents of an entire directory.

Also, when using the import wizard, I can parse the file into columns but cannot separate the title from the value - ie. System Name="AABBCCDD-XP"

use a derived column task and apply substring over System Name variable to get part you want.
where do you want to transfer files from?

PatrickMc
Starting Member

1 Post

Posted - 2009-07-27 : 13:52:36

ou may consider using a script to convert the files first into a combined CSV (Comma Separated Values) file, then import that CSV file into SQL Server. I wrote a quick script for you for a subset of your input file format.

I assume all company asset files are at a network share, say, "X:\*.txt" and have the following format.

quote:
Company Computer Asset Information Log File
Information valid as of Mon 07/06/2009
System Name="AABBCCDD-XP";
Serial Number="4325454";

Here is the script.


# Script import.txt
# Collect a list of asset files.
var str list ; lf -n "*.txt" "X:\" > $list
# Process each file one by one.
while ($list <> "")
do
    # Get the next file.
    var str file ; lex "1" $list > $file
    # Read file into a str variable.
    var str data ; cat $file > $data

    # We will get date, system name, serial number.
    var str date, sysname, sernum
    # ADD OTHER ITEMS AFTER THESES.

    # Date is the last word on line 2.
    var str line ; lex -p "2" $data > $line ; wex "l" $line > $date

    # System name is on line 3, between =" and ";
    lex -p "3" $data > $line ; stex "^=\"^]" $line > null ; stex "[^\";^" $line > null ; set $sysname = $line

    # Serial number is on line 4, between =" and ";
    lex -p "4" $data > $line ; stex "^=\"^]" $line > null ; stex "[^\";^" $line > null ; set $sernum = $line

    # Show a CSV entry for this item.
    echo $date "," $sysname "," $sernum
done

The script will show the data in CSV format in its output. Script is in biterscripting ( http://www.biterscripting.com ) . Save the script as C:\import.txt. Start biterscripting. Call the script with the following command.

script import.txt

You can save the output to a file by redirecting the output, such as follows.

script import.txt > "inventory.csv"

I have added ample comments so you can convert this algorithm into another scripting langauge if necessary. Also, the output file can be imported using sql directly from a biterscript or any other script.

Hope this helps.

Patrick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources