Importing text file using schema.ini into Linked Server

Question

Hi All,

I importing a text file into a linked server using schema.ini file. My problem arises when i import a custom delimited(|) file which contains 20 columns. The query is not delimiting.

Here is my schema.ini file..

[PRE_E_RECHARGE#txt]
ColNameHeaders=True
CharacterSet=ANSI
Format=Delimited(|)
col1	RECH_DATE_TIME	datetime
col2	RECH_DATE	datetime
col3	CHARGINGPARTYNUMBER	text
col4	CUST_CATEGORY	text
col5	CONNTYPE	text
col6	MRPVALUE	double
col7	TALKVALUE	double
col8	SERVICE_TAX	double
col9	PROCESSING_FEE	double
col10	EXTRA_TALK_TIME	double
col11	RECHTYPE	text
col12	IN_VCH_ACT_BATCH	text
col13	IN_VCH_DESC	text
col14	CIRCLE	text
col15	VOUCHERSEQUENCE	text
col16	VOUCHERBATCHNUMBER	text
col17	RESERVED_COL	text
col18	VOUCHER_TYPE	text
col19	RECHARGING_RETAILER	text
col20	TRANSITIONID	text

please help me how to resolve...

---

GSKR

Answer 1

What software do you use to import?

SSIS Import/Export Wizard:

http://www.sqlusa.com/bestpractices/ssis-wizard/

You can also use bcp or BULK INSERT:

http://www.sqlusa.com/bestpractices2005/notepad/

A popular strategy: import to a staging table, move it properly formatted to final destination table(s).

---

Kalman Toth SQL 2008 GRAND SLAM
Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 2

Hi Kalmon,

Thanks for your reply. I am trying through linked server

---

GSKR

Answer 3

>> I am trying through linked server

Linked Server? You can use Import Export Wizard or SSIS to import data. Can you explain how you are importing the data?

Regards
Satheesh

Answer 4

Hi Satheesh,

I am trying through linked server. i have created linked server as below..

EXEC sp_addlinkedserver @server = N'test_db', @srvproduct=N'Jet 4.0', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'E:\Jan\Test_DB', @provstr=N'Text' GO

when i executed the query its returing as

---

GSKR

Answer 5

>>When i executed the query its returing as

what is the query you executed? and what is the source file look like

Regards
Satheesh

Answer 6

Source file PRE_E_RECHARGE.txt vertical delimited text file.

Query is.

select * from test_db...PRE_E_RECHARGE

---

GSKR

Answer 7

Import Export Wizard :-
http://msdn.microsoft.com/en-us/library/ms140052.aspx

SSIS to import data:

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/2d767954-f986-4dd6-8791-eb4479a56149

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 8

Hi Ahsan,

Thanks for your reply. I know how to import via wizard. i want to link the text file which is in vertical bar delimiter file.

---

GSKR

Answer 9

Hi GSKR,

Since we can execute the following command to get the file content, I suggest using SUBSTRING and CHARINDEX functions to split the result, and then insert them into the table.

select * from test_db...PRE_E_RECHARGE

SUBSTRING (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms187748.aspx

CHARINDEX (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms186323.aspx

 

---

Allen Li
TechNet Community Support

Answer 10

Thanks for your reply. I know how to import via wizard. i want to link the text file which is in vertical bar delimiter file.

So why don't you use the wizard? BULK INSERT?

Once you have the text in a staging table, it is easy to split:

http://www.sqlusa.com/bestpractices/training/scripts/userdefinedfunction/

Linked server to a flat file your goal?

---

Kalman Toth SQL 2008 GRAND SLAM
Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 11

No i want in only linked server...

---

GSKR

Answer 12

Here is what I don't get. There are so many ways to upload a flat file into the database, why you insist on a way you cannot make it work?

>No i want in only linked server...

What is the server you intend to link? ORACLE? DB2? Another SQL Server?

You can link to a .csv file, but I don't recommend it:

http://forums.databasejournal.com/showthread.php?38583-CSV-Linked-Server

If you want to read a flat file, you can use (among others) OPENROWSET for example:

http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/5869d247-f0a0-4224-80b3-ff2e414be402

---

Kalman Toth SQL 2008 GRAND SLAM
Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 13

Kalman,

This is already answered, Can you please  merge this thread to 

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/4c02f3bc-42dc-40ea-afb0-f5f7176ec8a9/#378af362-0f68-4821-8b87-7811fde3cba2

Regards
Satheesh