Importing Text File: How to dynamically change the row delimiter

Hi,

I have a dts package that imports a number of text files into a SQL Server 2000 database table. The package has been set up to accept a text file with a row delimiter of carriage return and line feed ({CR}{LF}). Some of the text files I receive only have a line feed ({LF}) as the row delimiter and the dts package fails the file. Is there an activex script i can use that will scan the file and change the row delimiter as required?

i was going to use the filesystemobject which allows me to read a line at a time, however the Readline method doesn't read the new line character. The text files are too big to read into one variable and then do a replace.

Any help would be appreciated

I am using SQL Server 2000 SP3, Windows Server 2000 and Windows XP Professional. All systems are fully patched

Regards JustinThe solution is to identify and change the EOL character that is used in the file before the file is passed through the dts package. The scripting object doesn't appear to contain anything useful that will identify the EOL character so I have come up with this routine in VBA rather then VBScript. It will accept a user defined number of characters in a text file for and returns the EOL character (only a carriage return or line feed).

Public Function gIdentifyEOLCharacter(strFileName As String, _
lngNumOfCharactersToCheck As Long) As String
' identifies the end of line character
Dim fsoSysObj As Scripting.FileSystemObject
Dim tStream As Scripting.TextStream
Dim strText As String, strEOLCharacter As String
On Error GoTo ErrorHere
Set fsoSysObj = New Scripting.FileSystemObject
Set tStream = fsoSysObj.OpenTextFile(strFileName, ForReading)
strText = tStream.Read(lngNumOfCharactersToCheck)
If InStr(strText, Chr$(13)) Then _
strEOLCharacter = strEOLCharacter & "{CR}"
If InStr(strText, Chr$(10)) Then _
strEOLCharacter = strEOLCharacter & "{LF}"
gIdentifyEOLCharacter = strEOLCharacter
ExitHere:
Set fsoSysObj = Nothing
Set tStream = Nothing
Exit Function
ErrorHere:
MsgBox "Error In: Module 'basSupportFunctions'" & vbCrLf _
& "Procedure: gIdentifyEOLCharacter" & _
vbCrLf & "Error Code: " & Err.Number & _
vbCrLf & "Error: " & Err.Description, vbExclamation, "Error Alert"
gIdentifyEOLCharacter = vbNullString
Resume ExitHere
End Function

I can't believe that there is nothing simpler in the scripting object that can return the EOL character - i have looked through the object model and can't see anything that is useful|||I think you can try FINDSTR with /G:file parameter where you'd have nothing but CRLF in the file. If FINDSTR returns a file name then it means that the specified file contains normal EOL combination. Else, - it's not. You can also have 2 files, one with CRLF and the other with just LF. So that if the first one produced 0 results you can do FINDSTR against the second and be certain that LF is the actual delimiter.|||This is just a wild guess but you might run a cmdshell and do:

type inputfile.txt>newfile.txt

before importing. Type may convert LF to CRLF.

I also wonder if you could create a CR placeholder column with a default value of null and always specify LF as the line terminator.

If these files are coming from an FTP transfer, set the type to Ascii instead of bin in FTP to get CRLF terminators.

Importing text file removes decimal separator

Hi,

I'm trying to import a semi-comma separated text file into a SQL db. I have a field in the text file that contains decimal number. As a decimal separator it's used a comma (15,35). When i use a DTS package to create a destination table and import all rows, the field is created as a float field. In this field the decimal comma is removed so the number in SQL becomes 1535. If I change the decimal separator to (.) i works OK. But I need to get it work with comma as decimal separator. In the DTS package the field form the text file is recognised as varchar (8000). Any ideas?

Ingar

You have to choose appropriate locale which threats comma as a decimal delimiter. There is the drop-down control for choosing locales on the first page of the Flat File Connection Manager UI.

Thanks.

|||

Hi, thanks for your answer. I can see that I didn't specify which SQL version i was using. I use SQL 2000 not 2005. I know that the theme in this forum is SSIS but it also states "transforms/data flow" and I thought DTS packages from SQL 2000 was included here. I appologize if I have misunderstood this but english is not my first language.

So as far as I know the Flat file connection manager is not available in SQL 2000.

But thanks anyway.

Ingar

Importing Excel 2007 and/ or DBF files into SQl server 2005

Greetings,

I'm having a tough time importing some of my legacy database into sql.
I have a number of dbase (IV) files I need to get into SQL. I have tried building a SSIS package with either an foxpro oledb connection or a jet 4.0 one, none of them work bec. of inconsistencies in the data format in my tables (e.g. date fields, etc).

I have tried to save the .dbfs as excel 2007 files, taking advantage of the larger space that comes with '07. Problem is you can't use the import/export wizard with 2007 for some reason and I haven't been able to create a package with the access 12 oledb as I have read.

I have to get some crucial data out of that old system and into the new one and I can't seem to be able to import them properly.

Any hints on what I should do ? (maybe I'm doing something awfully wrong)

Thank you for taking the time to answer my question,
Val

If you have SP2 installed, you should be able to load data from Excel 2007 format using the I/E wizard. Do not use the Excel connection, but use the new OLE DB provider for Office 2007. You will need to set extended properties to "Excel 12.0".

Let me know if you need more assistance.

Thanks.

|||

Try by looking at this : http://msdn2.microsoft.com/en-us/library/aa337084.aspx and also you'll need to configure your connection manually to connect. Set up a Jet OLEDB Connection - point to your folder containing the DBase files. Click the "All" button and change the "Extended Properties" to "DBASE IV".

Importing Excel "Reports"

I'm trying to import data which from Excel which has been "prettied up" to make it readable to users. The report has a fixed number of columns and the data itself is well structured, but there are blank lines etc.

The problem I'm having is unless the first row of the spreadsheet contains or headers, SSIS fails with error "External table is not in the expected format." and the exception below is thrown.

So I thought I'd be clever and create a template spreadsheet with the same number of columns and set the first row to strings - I made all columns DT_WSTR thinking I could extract the numeric and datetime data from the actual rows which contain data and convert using a script. But SSIS simply ignores the numeric values from the spreadsheet - all I get is String or Date values and nulls for all numeric cells?

Dave

Error at Monthly Balance [Connection manager "Excel Connection Manager"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "External table is not in the expected format.".

Error at Data Flow Task [Excel Source [1]]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.

Dave,

You may find the DataDefractor SSIS Data Flow Source useful. It's main purpose is to allow you to extract factual and contextual information from human-readable reports and then flow this data into the SSIS pipeline. You can download a free beta at http://www.datadefractor.com.

Cheers,
Vassil

|||Thanks Vassil, Looks like a useful component.

Importing data from Paradox into SQL

I have a Paradox file which has 270 thousand records. If I open this db file through a paradox viewer it shows the correct number of rows.

I have an SSIS package which is suppose to read this db file and insert it into the SQL 2005 table. But when I execute this package it goes into a loop, reading the file over and over again. The package fails after inserting some 10 million rows giving the error msg 'Not enough space on temporary disk'.

On examing the data transfered into SQL there are duplicate rows.

I also used the import export wizard (thinking there might be some error in Package code) provided by SQL to transfer the data from .db to SQL but it has the same result(goes in a loop).

I would appreciate any help in this problem. Let me know if you have any other questions.

Thanks

Duplicate! Ignore!

Importing Data from Access 2002 in SQL Express

Hi All,

At the moment i have a piece of software that uses Access to store the data into a number of tables.

I am developing a new piece of software that has been built around SQL express and need to upgrade users that are using access. I have managed to write the installation to install etc and also the start of a script to insert all of the tables. I cant work out how to get the data into SQL express using a script.

Basically the access db (CentralDb) will be stored a a given location C:\Program Files\CentralDB.mdb

The tables within the SQL version are indentical to access but i need a way of pulling out the information stored and putting it into SQL, and as there is no DTS i am becoming stuck on how to automate the process.

Any ideas?

Thanks in advance

Martin

Hi,

here is a good article to refer http://blogs.msdn.com/euanga/archive/2006/07/20/672272.aspx you may also use SSMA to migrate data from Access to SQL Express.

Hemantgiri S. Goswami

Importing Data from access 2000 in SQL

Hi All,

At the moment i have a piece of software that uses Access to store the data into a number of tables.

I am developing a new piece of software that has been built around SQL express and need to upgrade users that are using access. I have managed to write the installation to install etc and also the start of a script to insert all of the tables. I cant work out how to get the data into SQL express using a script.

Basically the access db (CentralDb) will be stored a a given location C:\Program Files\CentralDB.mdb

The tables within the SQL version are indentical to access but i need a way of pulling out the information stored and putting it into SQL, and as there is no DTS i am becoming stuck on how to automate the process.

Any ideas?

Thanks in advance

Martin

I do not know if you can write it with the help of query. Yes, if you can import the Access data, that would be great.

Well, you might like to write a small application for your data migration. The application should be able to read data from your access database and will push the data in SQL.

|||Create a "linked server" to the access file, and just use TSQL queries to pull the data from the linked server.|||

Excuse my ignorance, but where can i find the linked server option ?(i am using SQL 2005 Managment Studio Express) and cant find the option to link servers (the version i am running also has no help!!)

Thanks Again

Martin

|||It is under: ServerName -> Server Objects -> Linked Servers. Right click and select "New Linked Server".

Give it a name, like "oldaccess". Then select the MS Jet 4.0 OLEDB driver. Put in the full path of the Access database in the "Data Source".

To insert into your SQL table use:

INSERT INTO tablea
SELECT * FROM oldaccess.tablea

See this tutorial: http://www.databasejournal.com/features/mssql/article.php/3103331

Importing binary data tables?

My apologies in advance for hitting the forum with such a basic question...

I've got a large number of raw binary data tables generated by some external software that I'd like to import into a SQL database. I've set up the database tables to hold the information and assigned the table columns the appropriate data types, but I can't figure out how to import the data.

From what I can see there isn't a data import 'wizard' that supports raw binary, and my limited experience with bcp has given me the impression that it only supports ASCII tables. I find it hard to believe that there isn't a DTS procedure to handle this... but I'm definitely stuck...

Thanks,
-fpsivYou can import data by using bcp or bulk insert to text or image fields and then parse data if it needs.|||Thanks for the quick reply snail...

I was working with BULK INSERT via the Query Analyzer... but ran into a brick wall in trying to set up the format file. I can't seem to get the 'column collation' parameter set correctly. I thought that 'collation' applied to character data... is there a correct collation for raw binary?

Thanks,
-fpsiv|||Check this : import image, I was trying to the same for binary file - it works too.

http://www.dbforums.com/t926676.html|||Thanks snail... got it working! I really appreciate your help!

Cheers
-fpsiv|||hmmml ! I tried the same query but it didn't worked :

CREATE TABLE Pictures
(
Pic_id smallint
IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
Images nvarchar(5000)
)

BULK INSERT Northwind.dbo.[pictures] FROM 'c:\bridge.jpg'
WITH (
FORMATFILE = 'c:\bcp.fmt'
)

------------
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.

Did I missing something?

Thanks