Import A Text File To A SQL Table Question

Feb 6, 2008

Greetings all!

I am a relative newbie to SQL but I've written many queries for vb.net/.net code...I'm not an absolute beginner.

I'd like to import a text file into a sql database so that I can use SQL Reporting Services to report on the data. Here is a sample of the first 8 textfile records. All of the 6 potential database fields are separated by a comma and no spaces:

1,12/4/06 4:12:11 PM,67.13,70.50,71.56,8.23
2,12/4/06 4:17:11 PM,67.13,70.50,71.56,8.33
3,12/4/06 4:22:11 PM,67.19,70.69,71.69,8.19
4,12/4/06 4:27:11 PM,67.19,70.63,71.69,8.18
5,12/4/06 4:32:11 PM,67.19,70.69,71.75,8.05
6,12/4/06 4:37:11 PM,67.19,70.69,71.69,8.03
7,12/4/06 4:42:11 PM,67.19,70.63,71.69,8.05
8,12/4/06 4:47:11 PM,67.19,70.63,71.63,8.02

The description for field datatypes of the first record above is:

1 (this is an autonumber, should be a number for ordering)
12/4/06 4:12:11 PM (date and time, can be converted to text if necessay)
67.13 (number, 2 decimal spaces)
70.50 (number, 2 decimal spaces)
71.56 (number, 2 decimal spaces)
8.23 (number, 2 decimal spaces)

The textfile is big, 97K records. I have SQL 2005 installed on my PC.

Can anyone out there please help me with the import or SQL statement to create a SQL table from this? Any help would be greatly appreciated!

Thanks and have a great day - gad1



View 5 Replies


ADVERTISEMENT

How To Create Table On Text File Import?

Sep 27, 2007

Hey guys,
I have a dilemma and hope someone can help.

I don't know of any utilities or commands in SQL that do this but I hope someone does.

What I need to do is something like a bcp import a text file in. I can do that with DTS as well. But what I wanted to do is create a table on the import. So lets say, I am importing a tab-delimited file with column names as the first row that is called ax.txt. On import, it would create the table ax with the column names in the file and then import the data into that table.

I hope I explained it clearly. Please let me know if there is anything I can use to do this without writing lots of code.

I have an idea how to do it the long way but hope there is a utility that already does it.

Thanks in advance.

View 10 Replies View Related

Import A CSV Delimited Text File Into A Table

Jan 18, 2007

Hi,

Could you help me to write a script to import a CSV delimited text file into a sql server table.?

Thanks,



carlos

View 2 Replies View Related

Import Text File To Sql Table In Sql Server 2005

Mar 7, 2008

hi guys
i need  to import text file to sql table in sql server 2005 ...using query how do i import text file to sql table .......................
 i need query
i dont want go Import/export options

View 1 Replies View Related

How Can I Export/import Data On Table To/from Text File

Dec 24, 1998

I needs export data on table to text file so I can process this data
with another database engine ie. Informix.

Can anybody help me to solve this problem ?

View 2 Replies View Related

How To Import Text File Using The Exising Table Schema

May 28, 2008

Hello;

I have table schema and text data file. After create the table, and then import the text data file. However, the text file always overwrite the table schema.

How could I using existing table schema to import the text file?

Thanks,

View 10 Replies View Related

Import Data From Text File Into A Temp Table In Stored Proc

Oct 1, 2001

Hey,
can one of you please show me how to import data from a text file into a temp table in a stored proc.
thanks
Zoey

View 1 Replies View Related

Text File Import

Apr 17, 2000

SQLServer 7.0

Hi,

I have a small project on that involves importing a series of csv files held within an ftp directory into our Datawarehouse. Every day a series of csv files will be added to the directory. These will be named something like:

Audit1.csv,Audit2.csv etc.

I would like to automate this process as this can involve up to 400 files at a time. The proecedure would need to identify a valid file, import it into the database, delete the file and then move onto the next one.

Does anyone know of a way to achieve this? I was thinking along the lines of using a cursor and bcp but I'm not sure how to identify these files to the database i.e. how do i make it step through the directory and process the files?

Any help would be greatly appreciated.

Thanks

Rob

View 1 Replies View Related

DTS Import Text File

Oct 20, 1999

In a DTS package I have a text file import object, a data pump, and a SQL object. The text file import object has been set up to splice a 500 character wide file into 20 columns. The data pump task does a copy column for all the columns into the appropriate table. What I need to do is have a way of changing the file name I specify in the text import object. I have 12 months worth of data in seperate files (DBF0199.TXT, DBF0299.TXT, DBF0399.TXT, etc..)
which all use the same format. Is there a way to change the text import objects file name inside the package using an active script task or something?

Any help is greatly appreciated.

Thanks,

Todd

View 4 Replies View Related

Using DTS To Import Text File

Dec 5, 2002

I'm trying to import a fixed field text file into SQL Server using DTS but everytime I go past 3640 characters, I am not able to add, delete or move column breaks after that. Is anyone else experiencing this problem and know of a work around. Any help would be appreciated. Thanks!

Using SP2 on SQL Server 2000.

View 3 Replies View Related

Import Text File

Sep 14, 2004

Is it possible to handle text files i MS SQL 2000 in which fields contains single quotations and also are terminated by a single quotation

Like this:
1,2,'John's',1,2

John's contain a single quotation

Or is possible to rewrite file using MS SQL to use double quotation
Like this;
1,2,"John's",1,2

Hope that someone can help me out here, and doing it using MS SQL

At the time, I'm handlig it using MySql, but I would prefer using MS SQL

Regards
Carsten H.

View 4 Replies View Related

DTS Import From A Text File

Jul 15, 2002

Hi All,
I'm having a problem in importing an Excel file into SQL server 2000.Here is the scenario with my data.
One of the column has got the mixed data which is putting null's in the SQL server table in some rows.I found in the MSFT Technet that it is a bug in SQL server 7.0/2000.The workaround for it ( according to MSFT ) is to get the data into text file and import into SQL server.
Now the question is , my data contains some currency fields and numeric fields in addition to the char and date fields. When I'm importing the table using DTS wizard , it is failing. I'm trying to use conversion functions like cdate and clng etc . Still the DTS is failing.
What I noticed is when I try to import into a table with data type varchar for all columns, it is working fine.But the data is of no use.
I would appreciate if any one can help me out in solving this problem.
Thanks,
Sammy.

View 1 Replies View Related

Text File Import

Jan 20, 2005

Quick advice question. I import lots of text files -- many with 50 plus data columns. Few come with a table layout other than perhaps the first row having a set of column names.

When I go to pull them into SQL server the columns default to varchar 8000. Is anyone aware of a tool (as a part of SQL Server or otherwise) that can scan a column of data and recommend a data type and size.

Appreciate any recommendations.

Ray

View 3 Replies View Related

IMPORT TEXT FILE

Mar 26, 2007

All,
What will be the painless way to import large fixed field text file that has more than 260 columns in sql table using dts pkg?

Suggestions?

View 4 Replies View Related

DTS Import Of A Text File

Jul 20, 2005

I have created a DTS that imports a text file to by data table. I geterrors when ever I run this since there are fields in the table thatare numeric. I understand that I need to create an activeX script toimport those fields. DOes anyone have any guidance?

View 1 Replies View Related

Text File Import With DTS

Jul 20, 2005

Does anyone know if it's possible to use the wizard or DTS Designer toaccept a source file with the following simplified format:<field1label>: <record1field1value><field2label>: <record1field2value>- - - - - - -<fieldNlabel>: <record1fieldNvalue><field1label>: <record2field1value><field2label>: <record2field2value>etc.i.e. each input record is delimited by {LF}{LF}, and each column by {LF}. Orwill it be necessary to write a Perl script (say) to convert it first into a..csv file?Thanks,Dave--************************************************** **********************Dave Stone e-mail: Join Bytes!Computing Services Telephone: +44 131-650-3314University of Edinburgh Internal ext: 503314Main Library, George Square FAX: 0131-650-3308Edinburgh EH8 9LJ************************************************** **********************

View 2 Replies View Related

How To Not Import A Text File Twice

Jun 19, 2006

Something I find myself wanting to do frequently is the traditional foreach loop looping through a directory of files and importing (which works great in SSIS) - Only I don't want to import data I have already imported.

In a previous job I used Perl for thing like this and the structure would be as follows:

For Each File:

1. Get filename and timestamp

2. Query db table with list of already imported filenames and timestamp. If Filename not in the table or is in table with older date return 1 to import or if file already imported return 0

3. Based on the result of step 2 either import or skip to next file.

Any recommendations how to do something similar in SSIS? I run stuck when I try to get the timestamp of the file and I also can't figure out how to do the conditional inside the foreach container... I am also open to other ideas on how to only import files I have not already imported.

View 5 Replies View Related

DTS Import Text File

Apr 19, 2007

Hi i'm new to DTS and need to be able to import a text file into a table each day.



The main problem I have is the file is datestamped so the name of the file changes each day.



Today it would be called file20070419.txt tomorrow it would be file20070420.txt. When I select a text file as a source I have to pick a valid file ??? how can I get round this ???

View 1 Replies View Related

How To Import A Text File With Transac-sql

Mar 27, 2007

Hi allI am looking for examples of scripts that will help me doing these things:
 - import a text file delimited with the character "*", representing a new month of data, for example data from march 2007
- create a new table with the structure of an existing one to import the data, for example  Data_March_2007
- alter an existing totals table adding a new column for the new moth imported, adding a new colum for the month of March 2007.
 
 

View 1 Replies View Related

Import Text File Problem

Sep 26, 2007

Hello Everyone and thanks for your help in advance.  I am having a problem importing a text file into SQL Server 2005 and can't figure out where the issue is.  The file is in CSV format with the text delimiter field as a ".  When attempting to import the field into a SQL Server 2005 table, the import fails due to numerous truncation errors.  I have tried importing into an existing table, but have also tried importing and allowing the import to create the table.  I receive the same failures in both cases.  For whatever reason, when allowing the import to create the table, each column is created as a nvarchar(50) even thought the column sizes vary widely.  Oddly enough, when importing into a SQL Server 2000 table with the correct layout, the file imports perfectly fine, thus verifying there is nothing wrong with the data source.  It also creates the table with appropriate column sizes in SQL 2000.  I'm really at a loss as to what is going on.  Any assistance woul dbe greatly appreciated.  Thanks.

View 4 Replies View Related

Schedule Job For Import Text File

May 28, 2002

This is what I am going to do:

Everyday, there are users FTP the text files to a directory in the server. During the night, a job will be run to import these text files to a table.

First, the job need to read the file name, then open the file, read the first line and insert to the table until then end of the file. Then the second file will be read ... until no more file to be read.

I am using VB to read the file name and open the file, and my question is how to pass the file name to the second step which is in T-SQL?


Any experience to be shared in this area ?

Thanks,
Rachael

View 3 Replies View Related

Import From Text File Into Two Tables

Jul 16, 2002

Quick Question!

If you have both invoice header lines and invoice detail lines in a comma delimited file, how can I get the data in the file to be imported into two different tables. I can produce a text file eg:

1,20,10/03/2002,39 High Street Any Town,,
2,20,Fluffy Slippers,2,Red,10.99
2,20,Pyjamas,3,Black,15.99
2,20,Trousers,1,Lilac,24.99
1,21,10/03/2002,11 Gibson Close,
2,21,Sandles,1,Black,12.99
2,21,Shoes,4,Blue,23.99
1,22,13/03/2002,45 Mill Street,
2,22,Womble Feet,4,White,16.99
2,22,Glass Slipper,1,Transparent,23.99

Lines with 1 in the first column should go in the InvoiceHeader table
Lines with 2 in the first column should go in the InvoiceDetails table.

I have tried with DTS but to no avial - ActiveX scrips in the Transform Data Task can only seem to access one data destination - it one table not two.

Any Ideas?

Julia

View 3 Replies View Related

Import Text File Problem

Aug 20, 2002

I have a fixed field text file I am trying to setup an import for SQL 2000. If I use Access 2000 the file reads fine, but if I use the SQL Import feature or DTS, SQL doesn't line up the records correctly, I tried all the combinations of row returns with no luck, but Access works just fine. ANy ideas?

View 1 Replies View Related

Import Text File In SqlServer ...

Oct 21, 2007

dear all,
I have a text file that contains data that i need to insert into sql server... the file size is about 800 MB .. and contains about 17,000,000 lines ..
some one told me that there is a way in sql server to import this data automatically by writting some scripts ...
the file looks like this

xxxxxxxxx
xxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
yyyy .. yyyyy "I Need only These fields (the Ys).. I don't care about the rest of the file"
yyyy .. yyyyy
xxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxx

any help is really appriciated

View 3 Replies View Related

Optimise Import Text File

Mar 11, 2008

Hi,

Please can you help me,

I have created an database with 3 fields

View 3 Replies View Related

Optimise Import Text File

Mar 11, 2008

Hi,

Please can you help me,

I have created an database with 3 indexed (not clustered) fields
key : bytes 100
error : integer
status : bytes 6
I would like to know how to import 3 million or more records quickly.
If I don't index the the fields, the operation takes 3 hours. With indexed fields it takes overnight. Therefore, how can i optomise the import operation from a .txt file.

Thanks and regards.

View 3 Replies View Related

Import Text File To Sqlserver

Nov 9, 2007

Hi,
I am using DTS to import data from text file to database tables in SQL server2005.
there is one big column thay I dont know what data type I should use to import it, I tried both nvarchar(MAX) and varchar(MAX).

but I am getting the following error:

for nvarchar(MAX) , getting the below error:

Error 0xc02020ed: Data Flow Task: Columns "Column 2" and "VendorDesc" cannot convert between unicode and non-unicode string data types.
(SQL Server Import and Export Wizard)

for varchar(MAX) , getting the below error:


Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 2" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)



Could you please let me know what data type I can use to handle this column?

Thanks,
Gelareh

View 1 Replies View Related

Import Text File Into Mssql

Dec 23, 2005

the text file format use the length of character to define the field

for example,
0001130130HAUT BAGES AVEROUS 03

9 chars <0001130130> is a field
1 char <H> is a field
20 chars <AUT BAGES AVEROUS 03> is a field

one record by one record store in text
no space, no symbol, no line break between each record
i must use the length of char to define it.

Please help me how to import into mssql

View 1 Replies View Related

Where's The Text File Import Wizard?

Oct 22, 2007

This was a no brainer in SQL Server 2000 but I just can't figure out where the heck it is in SQL Server 2005 Enterprise Edition. Where is the wizard that will import a text file and create a database from it?

View 3 Replies View Related

Import And Overwrite DB From Text File

Jul 19, 2007

First off, I am not a DBA, not even remotely close. Anywho, I have been given the task of figuring out how to import from a comma delimited text file into 2 columns of an existing database. The task is as follows:

- A daily text file is created by a Unix DB and placed on a folder local to the SQL Server.
- I am to take this file and import into an existing MS SQL2005 DB that has 3 columns.
- AccountID, AccountName, DateRecordCreated
- The imported data has to overwrite all existing SQL DB data.
- This is to run automated on a daily schedule.

Being a SysAdmin, this sounds super simple to do but I have wasted 2 full days in trying to figure out how to make this happen using SSIS. All I want to know is if I am in the right track in focusing on SSIS for a solution. Any additional How To's would be greatly appreciated. BTW, the text file looks something like this...

AccountID,AccountName
A123456,Joe Smith, M.D.
A234567,John H. Dude,M.D.

Thanx much

View 1 Replies View Related

Import Tabbed Text File To The Sqlserver

Jun 1, 2008

I m working with tabbed text file, i manually import it to databases and so on.. i want to make a function which automatically call that text file and convert it to sql server database..  anybody knows how to achieve this..thx 

View 1 Replies View Related

Text File Import Into One Record Very Urgent

Nov 15, 2000

Hi Every one.

I have a text file (BEA.Txt) which contains the description of the products. I have the product table as below

Product_Code VarChar(3)
Description Text

I need to add the text from text file BEA.TXT into product table for Product_code = BEA.

Please help me
Thanks
Lilly

View 3 Replies View Related

TO ALL SQL Gurus TEXT FILE IMPORT VERY VERY URGENT

Nov 16, 2000

Hi Every one.

I have a text file (BEA.Txt) which contains the description of the products. I have the product table as below

Product_Code VarChar(3)
Description Text

I need to add the text from text file BEA.TXT into product table for Product_code = BEA.

DTS DOS NOT WORK !!!. IT ADDS SEVERAL RECORDS TO PRODUCT TABLE. IT CONSIDERS EACH CARRIAGE RETURN AS NEW RECORD IT DOES NOT HELP MEARLY

Please help me
Thanks
Lilly

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved