importing txt file to SQL with recordset

[EnvY]

Im trying to import a ; spaced txt file to SQL with the help of recordsets in asp, and having some problems with a text field that is over 256 chars.
Can i use a substr in my select from the txt file? how?

[Rob]

Are you using the 'ODBC Driver for Text files' ?

[EnvY]

Conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"HDR=yes;" &_
"Dbq=C:\temp;" & _
"Extensions=asc,csv,tab,txt;" & _
"Persist Security Info=False"

[Rob]

You may need a schema.ini file....

It's worth investigating.

I rarely use the text driver... and only then for csv files

[EnvY]

im am using a schema.ini.
All is working as it should but im missing the last chars from the text field that is over 256 chars, since the recordset can't hold more....
You know if the text driver suports substring select?

[Rob]

Can you post your code (in forum code tags) and a sample of your data?

I import records with longer fieldlengths with no issues.

Also, please post your schema.ini file

[EnvY]

Code: Select all

Set Conn = Server.CreateObject("ADODB.Connection")
Set obj_RecordSet = Server.CreateObject("ADODB.Recordset")

Conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
		  "HDR=yes;" &_
		  "Dbq=C:\temp;" & _
		  "Extensions=asc,csv,tab,txt;" & _
		  "Persist Security Info=False"

Dim sql

sql = "SELECT * from artikkeldata2.txt"
set obj_RecordSet = conn.execute(sql)

schema.ini:

[Artikkeldata2.txt]
Format=Delimited(;)
ColNameHeader=True
MaxScanRows=0
CharacterSet=ANSI


the recordset works as it should exept for the filed from the txt file with over 255 chars.

[EnvY]

i tryed to use a substing select to select the remainig of the filed, but dont know if the ODBC driver suports it...

Code: Select all

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Text Driver] Undefined function 'substring' in expression.

[Rob]

Can you post the first 10 lines of your text file? The data?

[EnvY]

"Doknr";"N1 Dokument Tittel";"N1 Kommentar Tittel";"N1 Revisjonstekst";"N1_Kommentar";"N2 Start Tekst";"N2 Slutt Tekst";"Varenummer";"Modellkode";"Mrp";"Nettovekt" ;"Basispris";"Attributt";"Beskrivelse";"Alfanumeri sk";"Numerisk";"Fys Beh";"Av Doknr";"Av Dokument Tittel"
111001-1-1;Tannhjulspumpe - Marzocchi - GHP;Tannhjulspumper serie GHP1, GHP2 og GHP3;Pumpene er enkle, robuste og rimelige. Kan monteres sammen med BORELLI forsatslager, elektrisk og manuell frikobling.;#Arbeidstrykk opptil 290bar. #Fortregningsvolum 1,4-52 cm3/r #Tandempumper fra lager #Høyregående som standard #Dreieretning skiftes enkelt #Standard aksling er Kon 1:8;;;GHP1D2;GHP1D2;A;;1610;P01;Trykk bar kont.;;290;21;111001-1-1-1;Tannhjulspumpe - Marzocchi - GHP - Datablad
111001-1-1;Tannhjulspumpe - Marzocchi - GHP;Tannhjulspumper serie GHP1, GHP2 og GHP3;Pumpene er enkle, robuste og rimelige. Kan monteres sammen med BORELLI forsatslager, elektrisk og manuell frikobling.;#Arbeidstrykk opptil 290bar. #Fortregningsvolum 1,4-52 cm3/r #Tandempumper fra lager #Høyregående som standard #Dreieretning skiftes enkelt #Standard aksling er Kon 1:8;;;GHP1D2;GHP1D2;A;;1610;P01;Trykk bar kont.;;290;21;111001-1-1-2;Tannhjulspumpe - Marzocchi - GHP - Artikkeltabell
111001-1-1;Tannhjulspumpe - Marzocchi - GHP;Tannhjulspumper serie GHP1, GHP2 og GHP3;Pumpene er enkle, robuste og rimelige. Kan monteres sammen med BORELLI forsatslager, elektrisk og manuell frikobling.;#Arbeidstrykk opptil 290bar. #Fortregningsvolum 1,4-52 cm3/r #Tandempumper fra lager #Høyregående som standard #Dreieretning skiftes enkelt #Standard aksling er Kon 1:8;;;GHP1D2;GHP1D2;A;;1610;P02;Trykk bar int.;;300;21;111001-1-1-1;Tannhjulspumpe - Marzocchi - GHP - Datablad
111001-1-1;Tannhjulspumpe - Marzocchi - GHP;Tannhjulspumper serie GHP1, GHP2 og GHP3;Pumpene er enkle, robuste og rimelige. Kan monteres sammen med BORELLI forsatslager, elektrisk og manuell frikobling.;#Arbeidstrykk opptil 290bar. #Fortregningsvolum 1,4-52 cm3/r #Tandempumper fra lager #Høyregående som standard #Dreieretning skiftes enkelt #Standard aksling er Kon 1:8;;;GHP1D2;GHP1D2;A;;1610;P02;Trykk bar int.;;300;21;111001-1-1-2;Tannhjulspumpe - Marzocchi - GHP - Artikkeltabell

[Rob]

Here is the connectionstring I use:-

Code: Select all

"Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
       "Data Source=" & server.mappath(".\") & _ 
       " Extended Properties=""text;HDR=Yes;FMT=Delimited"""

I think you will find the above JET text driver more reliable.

[Rob]

If the above doesnt work, then I have a plan.

[EnvY]

:P whats ur plan ?

[Rob]

ok.... read in the entire file to a string using the file system object.

Use the Split command to split the string using VBCRLF. This will give an array of records.

Split each record on the semicolon. This will then give an array of fields.

User constants to represent fieldnames:-
Doknr = 0
Dokument_Tittel = 1
Kommentar_Titte=2
Revisjonstekst = 3
... etc

Then just refer to the array using the constants:-
FieldArray(Doknr) wil return the info needed.

I hope this makes sense.

If not, post back and I'll will post up some code.

[EnvY]

could prob make it my self, but always love a code example if u got 1

[Rob]

I aint got one handy... but if you get stuck, let me know.

[EnvY]

and btw when i try to use

Code: Select all

conn2.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
           "Data Source=" & server.mappath(".\") & _ 
           "Extended Properties=""text;HDR=Yes;FMT=Delimited"""

insted of

Code: Select all

Conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
		  "HDR=yes;" &_
		  "Dbq=C:\temp;" & _
		  "Extensions=asc,csv,tab,txt;" & _
		  "Persist Security Info=False"

i get an error msg:

Microsoft JET Database Engine (0x80004005)
Could not find installable ISAM.


how do i fix this?

[Rob]

Never seen this before.

From what I can find out, you should look here:-
http://support.microsoft.com/kb/209805

Check out the resolution (for the text driver)

That should help.


THe workaround method I described above is fairly easy and would likely be much quicker than ODBC anyway. Arrays are lightning quick in ASP.

[Rob]

Try this code I just flung together:-

Code: Select all

'Set Up Constants
Const ForReading = 1
Const Doknr = 0
Const DokumentTittel = 1
Const KommentarTittel = 2
Const Revisjonstekst = 3
Const Kommentar = 4
Const StartTekst = 5
Const SluttTekst = 6
Const Varenummer = 7
Const Modellkode = 8
Const Mrp = 9
Const Nettovekt = 10
Const Basispris = 11
Const Attributt = 12
Const Beskrivelse = 13
Const Alfanumerisk = 14
Const Numerisk = 15
Const FysBeh = 16
Const AvDoknr = 17
Const AvDokumentTittel = 18

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile(Server.MapPath("./artikkeldata2.txt"), ForReading)
StrFileContents =  f.ReadAll

RecordArray = Split(StrFileContents,VbCrLf)
for recordCounter = 0 to uBound(RecordArray)
    FieldArray = Split(RecordArray(RecordCounter),";")
    for FieldCounter = 0 to Ubound(fieldArray)

        'do stuff with the field contents here
        'eg.  Insert the stuff into a DB
        'or write the values to the screen... eg
        'Response.write "Doknr = " & FieldArray(Doknr) & "
"
        'etc...etc
    Next
Next

[Rob]

you getting anywhere with this?