Importing Text file via Query and Schema File

G

Guest

I am trying to import a text file into a table using an SQL query to pull in
data from a text file without headers. I placed a schema.ini in the same
folder as the text file to define the columns. I tried the following query I
found in another issue but I get an error message syntax error in FROM
clause. It seems to not like the # character. Any suggestions?

INSERT INTO MyTable
SELECT Account_Nbr
FROM [Text;HDR=No;Database=G:\FINANCIAL\Jacocella\Net
Margin\Analysis;].[TICBAL 02_2006]#txt;
 
J

John Nurick

Hi John,

I can't remember the details and can't test it here, but have you tried
enclosing the whole filename in brackets

...].[;].[TICBAL 02_2006#txt];
 
G

Guest

Ok that fixed the problem, but now when I execute the query I get another
error message saying Account_Nbr is an unknown field. I checked my
schema.ini folder and it is there. Here is the line from the schema file:

Col1=Account_Nbr Text Width 10

Could it be that you can't utilize a schema file when using a query?


John Nurick said:
Hi John,

I can't remember the details and can't test it here, but have you tried
enclosing the whole filename in brackets

...].[;].[TICBAL 02_2006#txt];

John said:
I am trying to import a text file into a table using an SQL query to pull
in
data from a text file without headers. I placed a schema.ini in the same
folder as the text file to define the columns. I tried the following
query I
found in another issue but I get an error message syntax error in FROM
clause. It seems to not like the # character. Any suggestions?

INSERT INTO MyTable
SELECT Account_Nbr
FROM [Text;HDR=No;Database=G:\FINANCIAL\Jacocella\Net
Margin\Analysis;].[TICBAL 02_2006]#txt;
Click to expand...
Click to expand...
 
J

John Nurick

Certainly one can use schema.ini with a query. Do you have
ColNameHeader=False
in the [TICBAL 02_2006.txt] (or whatever the filename is) section of
schema.ini?

Also, what do you mean by "I checked my schema.ini folder"? Schema.ini
must be in the same folder as the file you are importing.

Ok that fixed the problem, but now when I execute the query I get another
error message saying Account_Nbr is an unknown field. I checked my
schema.ini folder and it is there. Here is the line from the schema file:

Col1=Account_Nbr Text Width 10

Could it be that you can't utilize a schema file when using a query?


John Nurick said:
Hi John,

I can't remember the details and can't test it here, but have you tried
enclosing the whole filename in brackets

...].[;].[TICBAL 02_2006#txt];

John said:
I am trying to import a text file into a table using an SQL query to pull
in
data from a text file without headers. I placed a schema.ini in the same
folder as the text file to define the columns. I tried the following
query I
found in another issue but I get an error message syntax error in FROM
clause. It seems to not like the # character. Any suggestions?

INSERT INTO MyTable
SELECT Account_Nbr
FROM [Text;HDR=No;Database=G:\FINANCIAL\Jacocella\Net
Margin\Analysis;].[TICBAL 02_2006]#txt;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Yes, I have ColNameHeader=False

I checked my schema file to make sure the Account_Nbr filed was in the file
and it was. The schema file is in the same directory as the text file.
Following is my schema file:

[TICBAL 02_2006.TXT]
ColNameHeader=False
Format=FixedLength
Col1=Account_Nbr Text Width 10
Col2=Field2 Text Width 1
Col3=Account_Descr Text Width 40
Col4=Field4 Text Width 1
Col5=TAS_Account_Type Text Width 2
Col6=TAS_Account_Type Text Width 1
Col7=TAS_Group Char Width 4
Col8=Field8 Text Width 1
Col9=FRB_Code Char Width 2
Col10=Field10 Text Width 1
Col11=Plan_Type Char Width 1
Col12=Field12 Text Width 1
Col13=Spread Single
Col14=Field14 Text Width 1
Col15=Avg_Book_Bal Double width 17
Col16=Field16 Text Width 1
Col17=Avg_Avail_Bal Text Width 17
Col18=Field18 Text Width 1
Col19=Interest_Amt Double width 10
Col20=Field20 Text Width 1

John Nurick said:
Certainly one can use schema.ini with a query. Do you have
ColNameHeader=False
in the [TICBAL 02_2006.txt] (or whatever the filename is) section of
schema.ini?

Also, what do you mean by "I checked my schema.ini folder"? Schema.ini
must be in the same folder as the file you are importing.

Ok that fixed the problem, but now when I execute the query I get another
error message saying Account_Nbr is an unknown field. I checked my
schema.ini folder and it is there. Here is the line from the schema file:

Col1=Account_Nbr Text Width 10

Could it be that you can't utilize a schema file when using a query?


John Nurick said:
Hi John,

I can't remember the details and can't test it here, but have you tried
enclosing the whole filename in brackets

...].[;].[TICBAL 02_2006#txt];

I am trying to import a text file into a table using an SQL query to pull
in
data from a text file without headers. I placed a schema.ini in the same
folder as the text file to define the columns. I tried the following
query I
found in another issue but I get an error message syntax error in FROM
clause. It seems to not like the # character. Any suggestions?

INSERT INTO MyTable
SELECT Account_Nbr
FROM [Text;HDR=No;Database=G:\FINANCIAL\Jacocella\Net
Margin\Analysis;].[TICBAL 02_2006]#txt;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I found the problem. Apparently the fields in the table I am importing to
needs to have the same name as the Schema file. Hopefully, I don't run into
new problems. Thanks.

John said:
Yes, I have ColNameHeader=False

I checked my schema file to make sure the Account_Nbr filed was in the file
and it was. The schema file is in the same directory as the text file.
Following is my schema file:

[TICBAL 02_2006.TXT]
ColNameHeader=False
Format=FixedLength
Col1=Account_Nbr Text Width 10
Col2=Field2 Text Width 1
Col3=Account_Descr Text Width 40
Col4=Field4 Text Width 1
Col5=TAS_Account_Type Text Width 2
Col6=TAS_Account_Type Text Width 1
Col7=TAS_Group Char Width 4
Col8=Field8 Text Width 1
Col9=FRB_Code Char Width 2
Col10=Field10 Text Width 1
Col11=Plan_Type Char Width 1
Col12=Field12 Text Width 1
Col13=Spread Single
Col14=Field14 Text Width 1
Col15=Avg_Book_Bal Double width 17
Col16=Field16 Text Width 1
Col17=Avg_Avail_Bal Text Width 17
Col18=Field18 Text Width 1
Col19=Interest_Amt Double width 10
Col20=Field20 Text Width 1

John Nurick said:
Certainly one can use schema.ini with a query. Do you have
ColNameHeader=False
in the [TICBAL 02_2006.txt] (or whatever the filename is) section of
schema.ini?

Also, what do you mean by "I checked my schema.ini folder"? Schema.ini
must be in the same folder as the file you are importing.

Ok that fixed the problem, but now when I execute the query I get another
error message saying Account_Nbr is an unknown field. I checked my
schema.ini folder and it is there. Here is the line from the schema file:

Col1=Account_Nbr Text Width 10

Could it be that you can't utilize a schema file when using a query?


:

Hi John,

I can't remember the details and can't test it here, but have you tried
enclosing the whole filename in brackets

...].[;].[TICBAL 02_2006#txt];

I am trying to import a text file into a table using an SQL query to pull
in
data from a text file without headers. I placed a schema.ini in the same
folder as the text file to define the columns. I tried the following
query I
found in another issue but I get an error message syntax error in FROM
clause. It seems to not like the # character. Any suggestions?

INSERT INTO MyTable
SELECT Account_Nbr
FROM [Text;HDR=No;Database=G:\FINANCIAL\Jacocella\Net
Margin\Analysis;].[TICBAL 02_2006]#txt;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Importing text with TransferText. It can't see my Schema.ini file 1
Importing Text Files Into Access 1
Import text file fixed 2
Importing Problem 1
schema.ini files 4
Import from text file with double quotes 3
Specification XML failed to validate against the schema 1
Importing Text file Issue 10

Top