Could someone help me with the following problem? Hours of googling
yesterday couldn't get me the answer. I'm using SQL 2000 and DTS and
trying to import a huge fixed width text file.
File is >1m rows and >200 columns and is defined by a proprietory (i.e. not
bcp produced) format specification of the form
Name Start Length
Fld1 0 20
Fld2 19 5
Fld3 24 53
and so on.
Tbe only way I've found to define the columns so that DTS can import the
file properly is to go through the wizard and click on the starts of each
column. I don't want to use bcp if possible (I did enough of that on
SQL7) - but surely there's a way to get DTS to read from a format file so I
don't have to click 200 times (with all the ensuing errors I could make).
I have a load (180,000+) of text files whose contents need to go into a SQL server database.Whats the best way of doing this? Using a c# console program and if so, using FileStream or StreamReader? Or using a feature of SQL server itself. The text files themselves are less that 1k and are literally less than 200 characters.The problem is, I've tried a WinForm and although I can detect what files are there, as soon as I attempt to open one for reading, everything stops working and won't insert anything to the database.
I have text data files from a third party and they use comma as field delimiters and enclose the text for each column in double-quotes. Not a problem for most of the data files until they start sending files where there is " within the column values. SSIS package fails with the error:
The column delimiter for column "Column 1" was not found.
Any ideas on how to resolve this issue will be greatly appreciated.Thankspcp
I want to import multiple text files into a single table. I know I have to use BCP or DTS. But, I want import all files at once, instead of one at a time. And the file names are in sequence, viz. file1, file2, file3 etc. Can anybody tell me, How I can achieve this.
I have to import 18000 text files into a sql database. Each file contains 10 fields and around 5000 records. I am currently doing this with DTS.
What I am wondering is this: Is DTS the most efficient i.e. quickest way to import all this data. Bearing in mind there is about 90 million records to import in all.
I would appreciate the benefit of somebody elses experience when dealing with this type of thing.
Hi, Can anyone help? Need to upload a text file to a sql database but keep getting errors. I'm creating a page that will allow users to to bulk import and update to a MsSql database. The users provide a text file every so often with new/update information. So i want to use a DTS package to transform the infomation, and create a table in the database, then check against existing/non existing records, if the record exist, update it, if not insert it. I'm using Visual Studio.Net, ASP.Net and coding in VB.Net.
Anyone know where i can find documentation/code regarding the above? I will be greatful for any help.
I thought i fixed this problem, as it WORKED about two days ago, but now I'm getting errors.
I have a series of text files, some are delimited, some are fixed width. I previously was able to import these files thru a dts package by creating the table in a stored procedure. Exple: CREATE TABLE [Pol_Dump073104] ( [Product_Type] varchar (12) NULL, [Benefit] float NULL, [Base_Premium] varchar (12) NULL, [Rider_Premium] float NULL, [Contract_Value] float NULL, )
I then import the text file into the table and then cast the float datatypes as money in a select into statement to do aggregate functions on the money fields. this worked previously, but now I'm receiving this: 'Error converting datatype char to money.' I tried to convert to float and received the same error, only with float where the word money was.
Sorry if this is in the wrong forum but i didn't know if i should put it in the Data Mining forum.
My problem is two fold.
Scenario:
I have 11 text files with standard data format. They range in size from 20megs to 300+ megs (containing 100's of thousands of lines of data)
I build a simple DTS Package to automate the importing of the text files into a database table with the same format as the text files.
Running the files through my DTS produced errors on all files. The error was : Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.
The error also gave an approximate position of the problem row. So i opened up a text editor that can handle large files and took out about 5000 records before and after the problem area. So i now had a new test file that had 5000 records and contained what "should" be the problem row. I ran this file and it imported correctly. It did not produce the error that i got previously. However it did import things incorrectly. Going through only one of the files i found several rows (over two dozen) that had half the data on one row, then the other half on the other row. Obviouslly there is a problem here with the way the data was exported. I wanted to know if anyone knows of any programs i can get that will analyze these text files for inconsistencies. I have considered writing one in C# but want to see if there is something already out there that can help me.
Basically i need to look at these text files and find Rows that are "incomplete"
the files come from a unix based system, use LF for row delims and Chr(29) for Column delimiters.
How do I automate importing "All Text Flat Files" into a SQL 7 table. The key is that there is no validation neccessary for the data and I do not want to manually import the data. I just to delimited the data and import it using either a script or a schedular of some type that can do it for me. Some Please Help
There is a "text" file generated by mainframe and it has to be uploaded to SQL Server. I've reproduced the situation with smaller sample. Let the file look like following: A17 123.17 first row BB29 493.19 second ZZ3 18947.1 third row is longer And in hex format: 00: 41 31 37 20 20 20 20 20 ”‚ 31 32 33 2E 31 37 20 20 A17 123.17 10: 66 69 72 73 74 20 72 6F ”‚ 77 0D 0A 42 42 32 39 20 first row™Ş—™BB29 20: 20 00 20 34 39 33 2E 31 ”‚ 39 20 20 73 65 63 6F 6E 493.19 secon 30: 64 0D 0A 5A 5A 33 20 20 ”‚ 20 20 20 31 38 39 34 37 d™Ş—™ZZ3 18947 40: 2E 31 20 74 68 69 72 64 ”‚ 20 72 6F 77 00 69 73 20 .1 third row is 50: 6C 6F 6E 67 65 72 ”‚ longer
I wrote "text" in quotes because sctrictly it is not pure text file - non-text binary zeros (0x00) happen sometimes instead of spaces (0x20).
The table is:
CREATE TABLE eng (
src varchar (512)
)
When i upload this file into SQL2000 using DTS or Import wizard, the table contains:
select src, substring(src,9,8), len(src) from eng < src ><substr> <len> A17 123.17 first row 123.17 25 BB29 493.19 22 ZZ3 18947.1 third row 18947.1 35
As one can see, everything was imported, including binary zeros. And though SELECT * in SSMS truncates strings upon reaching 0x00's, still all information is stored in tables - SUBSTRINGs show that.
When i upload this file into SQL2005 using SSIS or Import wizard the result is following: < src ><substr> <len> A17 123.17 first row 123.17 25 BB29 4 ZZ3 18947.1 third row 18947.1 25
This time table is half-empty - all characters behind binary zeros in respective rows are lost.
I stumbled upon this problem while migrating my DTSes to SSIS packages. Do you think there is some workaround, or i need to turn on some checkbox or smth else could help? Please...
Help - I need to import data into an existing table. Most import rows were unique, so I had no problem using DTS and appending. However, some import rows match existing rows except for one column/field that contains updated/new data, and I have to either replace the entire row with the imported row, or replace the individual field with the new data. How do I do that when there are many rows to import? It would take forever typing in all the data using UPDATE. Thanks in advance for your help!
Hello,I need to import a bunch of .csv files. The problem I am having is the"non data" information in the files creating bogus rows and columndefinitions. Here is an example of the csv file.CBOT - End-of-Day Futures Bulk Download 2001.2 Year U.S. Treasury Notes FuturesDateSymbolMonth CodeYear CodeOpen20010103ZTH2001102.0937520010104ZTH2001102.0312520010105ZTH2001102.28125In this case, there are bogues rows created with the text at thebeginning of the file, and also the column names get placed into a rowas well. My question is; how do you import the file and strip out the"non-data" data? so that only the actual data gets inserted into the db?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I have the original copies of the .MDF and .LBF of another database that has a bunch of data in that I'm trying to important into MSSQL Express for a project I'm working on. I no longer have a running version of this database just the .MDF and .LBF files and I have no clue what version of MSSQL they were originally created with. If anyone knows a way to import this database please help.
I am trying to import a CSV file into an SQL Server table with the OleDbDataReader and SqlBulkCopy objects, like this: using (OleDbConnection dconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mystuff\;Extended Properties="text;HDR=No;FMT=Delimited"")) { using (OleDbCommand dcmd = new OleDbCommand("select * from mytable.csv", dconn)) { try { dconn.Open();
using (OleDbDataReader dreader = dcmd.ExecuteReader()) { try {
using (SqlConnection dconn2 = new SqlConnection(@"data source=MyDBServer;initial catalog=MyDB;user id=mydbid;password=mydbpwd")) { using (SqlBulkCopy bc = new SqlBulkCopy(dconn2)) { try { dconn2.Open(); bc.DestinationTableName = "dbo.mytable"; bc.WriteToServer(dreader); } finally { dconn2.Close(); } } } } finally { dreader.Close(); }
} } finally { dconn.Close(); } } } A couple of the columns for the destination table use a bit datatype. The CSV files uses the strings "1" and "0" to represent these.When I run this code, it throws this exception:Unhandled Exception: System.InvalidOperationException: The given value of type String from the data source cannot be converted to type bit of the specified target column. ---> System.FormatException: Failed to convert parameter value from a String to a Boolean. ---> System.FormatException: String was not recognized asa valid Boolean. at System.Boolean.Parse(String value) at System.String.System.IConvertible.ToBoolean(IFormatProvider provider) at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaDatametadata) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaDatametadata) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) at MyClass.Main()It appears not to accept "1" and "0" as valid strings to convert to booleans. The System.Convert.ToBoolean method appears to work the same way. Is there any way to change this behavior? I discovered if you change the "1" to "true" and "0" to "false" in the CSV file it will accept them.
Hi, I got one more problem with importing csv files using .net. The problem is that the csv file contains double-quotation marks (""). For example, the record looks like: ...,Bearing Double "D" Flange,... And the result is: ... | Bearing Double | null (all following columns are null) The code is as following: string strCsvConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties='text;HDR=Yes;FMT=Delimited(,)';"; using (OleDbConnection cn = new OleDbConnection(strCsvConn)) { string strSQL = "SELECT * FROM " + strFileName; OleDbCommand cmd = new OleDbCommand(strSQL, cn); cn.Open(); using (OleDbDataReader dr = cmd.ExecuteReader()) {while (dr.Read()) { string str = Convert.ToString(dr[8]); } // Bulk Copy to SQL Server //using (SqlBulkCopy bulkCopy = new SqlBulkCopy(strSqlConn)) //{ // bulkCopy.DestinationTableName = strSqlTable; // bulkCopy.WriteToServer(dr); //} } } Any idea is highly appreciated. shz
I've been trying to import a TXT file into an SQL database and I'm having trouble making it work correctly. It is a ASCII text file with over 100,000 records. The fields vary by the number of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CITY is 32 characters, etc.)
I can import the file with DTS. I go in and select exactly where I want the field breaks to be. Then it imports everything as Characters with column headers of Col001, Col002, Col003, etc. My problem is that I don't want everything as Characters or Col001 etc. I want different column names and columns of data to be INT, NUMERIC(x,x), etc. instead of characters every time. If I change these values to anything than the default in DTS it won't import the data correctly.
Also, I have an SQL script that I wrote for a table where I can create the field lengths, data type etc. the way I want it to look, FWIW. This seems to be going nowhere fast.
What am I doing wrong? Should I be using something else than DTS?
Hi i've 6 files with the same name part but all have a different ext. The 17 always changes to the current week number
xbouns.A17 xbouns.B17 xbouns.C17
I want to import these files into a database table. So I've create a foreach loop and select the foreach file enumerater but am not sure if this is the right way to go about it some help woth be great thanks.
I have over three hundred text files that I need to import to SQLServer.Each is in the exact same format.I want to import tham as seperate tables.Is there any way to do it in one process?Regards,Ciarán
I am a bit new to SQL Server but not to DBA or programming per se. I am having difficulties getting either an Excel or Text flat file to import properly.
I guess it would be best to ask, using either SSIS or BULK INSERT, what options need to be entered for a typical excel flat file?
I have a package with a corresponding configuration file.
If I open Sql server management studio and i connect to SSIS and right click to import a package, and then select to store the package in SQL Server (not on the file system)....
What happens with the configuration file?
Does the import take the values from the configuration file and place them in the package which then is stored in SQL Server?
Or do I need to put the configuration file someplace on the SQL Server where the package is imported so it can access it when it runs?
I'm a bit confused about what goes on there?
For example, I tried using the build command and then running the manifest file to import using the wizard and when it does that it copies the configuration files to a default location within the c:program filesmicrosoft sql server directory.
I need to write a genric CSV importer. one set of CSVs define the formats of all teh other CSVs. The format of the second set of CSVs is not know at the time of creating this project/SSIS package.
Imagine having two sets of CSV files.
Each file in the first set of CSVs define the format of files in the second set of CSVs The first set always have the same format. Each row of the CSV file defines a field with a Name and Type. The first two columns of each row of in the first set of CSVs have a FormatName and index. So a simple file may have:
The second set of CSVs contains records that have to comply with the format define in the first set. So in this aprticular case, a CSV file in the second set may have records such as:
The problem I have is creating a generic SSIS package for this. The first task, loading the first set of CSV file is fairly simple. The CSV format is known. But the second task is a bit trickier.
Assuming I have SQL tables to load the data. One 'Fields' record for each row in the CSVs from the fiirst set. One 'Rows' record for each row in the CSVs from the second set, One 'Values' record for each value in each row in the CSVs from the second set, Something like:
TABLE Fields (
FieldID int IDENTITY, FormatName varchar(100), Position int, ColumnName varchar(100), ColumnType varchar(20) )
- Dynamically create a SSIS package based on the content of each CSV fiel of the first set and execute that package for each file in the second set, selecting the correct package to execute (all records within a CSV file belong to the same format). - Write a single SSIS package that iterates through the rows of the second CSVs, does a lookup for each value of each row to find the field name and make an insert into the DB - Other SSIS method? - Don't use SSIS to parse the CSV and call a custom C# task? - Don't use SSIS at all ?
I have a folder on an SFTP server in our internal network which gets flat files periodically (almost every minute) from another server. The file structure is like this: Flat File Format•Line 1—List of field names comma separated •Line 2—List of field type comma separated •Line 3—Data comma separated •Line 4—Data comma separated and so on... All the files will be like this without the .csv extention. There are a lot of fields and creating a column for each field manually will be a pain. Heres what I am trying to do: 1. Create a process that runs periodically on the server and imports data from files into the SQL server table. 2. Use the information in line 1 and 2 to create the schema of the table and then ignore those lines in every import (the data should be appended to the existing data). What are my options? Thank you all,Bullpit
I am new to SQL7 and I am trying to run a package that will import fixed text files in a folder and transform the data to a table. I am using a good example found in sywink.com for dynamically importing files in a directory, but I am getting an error message when there is more than one file in the folder. Also, I am able to display all the files during the loop process, but the file initially set for the source connection is the only one that transfers data. I have the close(Transform) connection checkbox option checked, and have tried other methods of closing the connection before the new source name is called, none have helped. Does anyone have any solutions to this type of problem? Or know of other methods used for my situation? Thanks James
I have a client who is sending me 800+ excel files each month with sales data. Each of the files is identical in structure, but has sales data for different stores. I receive all these files at the same time.
Is there a method with Data Transformation Services where I can have it work off of all the files in a given directory. I can set up DTS to work off of specific Excel files with no problem, but what I would like to do is set up a DTS so it could pull from each of the 800+ files.
Is this possible, or do I need to look at a solution outside of SQL to consolidate the Excel files first?
The Excel file would have columns similar to the following: store_id, zip_code, sales, transactions.
I want to write a DTS that will import a file every day. The problem is that the files is not named the same thing every day. There is a naming convention (SOMMDDYY.TRN) that it will follow. I want to import this file (which is a fixed width file) each day to a table (The table will be empty each day).
After it is imported, I want to look at the NAME of the file, and pull out the date portion of it. So, if the file is called SO122603.TRN, i want to pull out 122603, and then update my table with that date for every record. So when I am done, I will have a table that represents the file I imported, with one added column. This added column would be a Date/Time that has the date that was in the filename. How do I do this???
HI!I am importing .txt files. How can i check the errors? I have created alog file, but the problem is that i lose some characters.I import for example:CodeABCFZHJHNfrom a text file, but sometimes Code can be 4 caracters longI import this 3 characters long now. When i add the same structuredtext file with some rows lenght 4, it skips the last character, but iget nothing in the log file.please helpxgirl
HiI have multiple excel files of the same format in a directory.They are called book1.xls, book2.xls, book3.xls and so on.What is the easiest way to import the tab named sheet1 from each ofthe excel files to a databse using SQL server 2000 enterprise edition?Regards,Ciarán
I'm trying to input a few thousand flat files into a few thousand tables in a sql databaseim using integration services with a for each loop to read all the files in a directorythe problem is i can only insert the data from all the files into one tabledoes anyone know a way to do multiple tables? maybe using some sort of variable?
