Import Human-Readable text file into SQL Server 2000

Hello,

I am receiving a text file that is produced from a mainframe that is
out of my control. I am attempting to find a (hopefully clean) way to
import it into a SQL Server database in an automated fashion. I am
not really concerned about how many tables it requires or what the
schema looks like as long as the data remains related and ends up in
its respective fields (I will probably use scratch tables for this).

The data is given to me in a format that is meant to be printed out
and read by human eyes (in a text file). The format looks something
like this:

Begin File:
------------------------
1234 1234 1234 1234 XYZ Company 01/01/2003
......More stuff related to XYZ company for a couple of lines ......
......(this stuff can easily be parsed by position)......

MCARD VISA AMEX DISC
------------------------
TOTAL 11111.11 4444.44 5555.55 30.01
TRANS FEE .20 .20 .15 .15
TRANS AMOUNT 2222.22 888.89 833.33 4.50
DISC .0165 .0165 .0365 .0355
------------------------

ANOTHER HEADER

.........More stuff related to XYZ Company............

End File:

Well, this isn't the exact format, but just an example. The point is
that all of the data in each column is related and should end up in
the same record which is related to the parent record of XYZ Company
(or all in a single record in a single table if that is the closest I
can get).

Also, the rows are not always present. For example, if TRANS FEE
doesn't apply to anything in the row, then the entire row will
collapse and TRANS AMOUNT would be the next line after TOTAL.

I was looking at the bcp utility and dts, but dts doesn't seem to have
the performance capabilities (or reliability for that matter) I am
looking for. Bcp seems like it might work if there is some advanced
formatting commands that I can't find in the documentation - Anyone?

The best I can come up with is to use a high level language such as C#
or VB.NET to parse the text file into another text file that is comma
delimited, and then use the bcp utility (or bulk insert) to import it
into SQL Server where I can then use TSQL to manipulate it how I want.
I am trying to eliminate the high level language parse and just go
straight from file to database. Does anybody know an easier route?

TIAI would probably still go with the high level language. You have multiple
pieces of information in different formats relating to the same bigger
object. It looks as though you have a fixed length part at the top and then
break into a pivot table in the middle. I don't know any format file that
is going to decipher that for you.

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Sven" <sstorhaug@.webuniverse.net> wrote in message
news:67ca584a.0309302103.1eb4b10e@.posting.google.c om...
> Hello,
> I am receiving a text file that is produced from a mainframe that is
> out of my control. I am attempting to find a (hopefully clean) way to
> import it into a SQL Server database in an automated fashion. I am
> not really concerned about how many tables it requires or what the
> schema looks like as long as the data remains related and ends up in
> its respective fields (I will probably use scratch tables for this).
> The data is given to me in a format that is meant to be printed out
> and read by human eyes (in a text file). The format looks something
> like this:
>
> Begin File:
> -----------------------
--
> 1234 1234 1234 1234 XYZ Company 01/01/2003
> .....More stuff related to XYZ company for a couple of lines ......
> .....(this stuff can easily be parsed by position)......
> MCARD VISA AMEX DISC
> -----------------------
--
> TOTAL 11111.11 4444.44 5555.55 30.01
> TRANS FEE .20 .20 .15 .15
> TRANS AMOUNT 2222.22 888.89 833.33 4.50
> DISC .0165 .0165 .0365 .0355
> -----------------------
--
> ANOTHER HEADER
> .........More stuff related to XYZ Company............
> End File:
>
> Well, this isn't the exact format, but just an example. The point is
> that all of the data in each column is related and should end up in
> the same record which is related to the parent record of XYZ Company
> (or all in a single record in a single table if that is the closest I
> can get).
> Also, the rows are not always present. For example, if TRANS FEE
> doesn't apply to anything in the row, then the entire row will
> collapse and TRANS AMOUNT would be the next line after TOTAL.
> I was looking at the bcp utility and dts, but dts doesn't seem to have
> the performance capabilities (or reliability for that matter) I am
> looking for. Bcp seems like it might work if there is some advanced
> formatting commands that I can't find in the documentation - Anyone?
> The best I can come up with is to use a high level language such as C#
> or VB.NET to parse the text file into another text file that is comma
> delimited, and then use the bcp utility (or bulk insert) to import it
> into SQL Server where I can then use TSQL to manipulate it how I want.
> I am trying to eliminate the high level language parse and just go
> straight from file to database. Does anybody know an easier route?
> TIA

Import excel into SQL 2005 working in development but not in production

I have a simple code that uses a the file upload control to read an excel sheet and upload the data into a SQL 2005db.

I'm using Visual Web Developer and Sql's express edition to test it. It works fine when I test. However, when I push it up to the production server and try it via the any other pc it does not. The page loads fine. However, when it starts to upload it errors out.

Any reason why? I've never seen this happen.

Here's the code. Thanks in advance.

Protected Sub BtnUpload2_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles BtnUpload2.Click UploadTextDocument()End Sub Private Sub UploadTextDocument()Dim locationAs String = FileUpload1.PostedFile.FileName.ToString' Connection String to Excel WorkbookTry Dim excelConnectionStringAs String =String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", location)' Create Connection to Excel Workbook Using connectionAs Data.OleDb.OleDbConnection =New Data.OleDb.OleDbConnection(excelConnectionString)Dim commandAs Data.OleDb.OleDbCommand =New Data.OleDb.OleDbCommand("Select BuilderID,SeriesID,OptionLevel,CommunityID,PhaseID,PlanID,ElevationID,OptionID,CurrentSalesPrice,LocalComments,Active,DateAdded,DateAvailable,DateInactive,SalesPriceEffective,SalesPriceExpires,PreviousSalesPrice,CutOffNotBefore,CutOffNotAfter FROM [Data$]", connection) connection.Open()' Create DbDataReader to Data Worksheet Using drAs Data.Common.DbDataReader = command.ExecuteReader()' SQL Server Connection StringDim connectionStringAs String = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString' Bulk Copy to SQL Server Using bulkCopyAs SqlBulkCopy =New SqlBulkCopy(connectionString) bulkCopy.DestinationTableName ="ExcelData" bulkCopy.WriteToServer(dr)End UsingEnd Using connection.Close()End Using LBError.Text ="The spreadsheet was successfully uploaded."Catch LBError.Text ="There was an error. Check the spreadsheet for correct format."End Try End Sub

whats the exact error message ur getting?

|||

Hey Karen,

I don't get one. Debugging is off on the live application. I just get the error message at "catch".

I have a prospective client looking at it as we speak and don't want to make any changes to the web.config while they are viewing it...

|||

to catch error

use

try catch.. with and throw ex,.

one good possible reason is excel work sheet contains datatype that are not a part of datatypes of ur columns in tables. ..

there would not be any error, but the writetoserver would simply exit.. that's it..so use the old sheets u used in development server and try again.

Try printing., sheetnames and fieldcount.. in reader..

|||

Actually, I'm using the exact same sheet, same data, everything. I debugged it and still can't find any errors.

It only works from developement... I haven't tried it in a reader.

|||

How bout u try saving that file first and then transferring its contents to the database?

Regards

Karen

|||

Possible problems:

You don't have the correct version of Jet installed on the production server.

The process doesn't have read permissions at the location that the file is being written to.

try turning on remote errors in the web.config file so you can see exactly what the error is.

|||

Please provide the code topcopy the data/rows in the Excel work sheet to the sql server table.

Thanks and Regards

Ravi Shanker Maduri

Hyderabad

|||

Okay. I captured the error message. When I select the excel sheet from the folder on my desk top this message appears. The path is correct, as I said, it works fine when I do it from developent...

'C:\Documents and Settings\rednelo\Desktop\IPAddress_Mapping\test.xls'. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

|||

The problem is this line here:

Dim locationAs String = FileUpload1.PostedFile.FileName.ToString

You are using the filename that was on the client. You need the filename of the temporary file that the server created.

|||

Hi

I want to Import The Excel Data to the sql server using T-Sql Query ,i will run somany PL-sql statements for each project i want to automate it , i want to insert the default data to a table using excel sheet to the sql server table.

Please if any one know the solution for moving the excel data to the sql server using T-Sql Statement only.

Thanks and regards

Ravishanker Maduri

|||

Motley:

You need the filename of the temporary file that the server created.

All that makes sense. How would I get to the name of the temporary file?

|||

strINVSRC = Path.GetFileName(FlInvSrc.PostedFile.FileName)

|||

Hey Karen,

I tried what you have and I'm getting this error when I put in on the server. Did you run into this?

The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.
However, the same code in development gives me this:

The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

So, its not finding the file. Let me recap...

Dim locationAsString

'this works in development as I said before. So I know that was finding it at least on my local machine.

location = FileUpload1.PostedFile.FileName.ToString

What you had does not appear to find the file.

location = Path.GetFileName(FileUpload1.PostedFile.FileName)

Its somewhere between the two...

|||

Take a look at this link some one gave it to me when i had the same error message

http://forums.asp.net/t/1034337.aspx

I have done it this way..

First i am getting the name of the file...

Dim strFilePath,location as string

StrFilePath = Path.GetFileName(FileUpload1.PostedFile.FileName)

and then

location = Server.Mappath("The directory where u wanna store the file" & StrFilePath.

hope this helps..

Regards

Karen