Convert text file to sql server compact database (.sdf)

sam

Hello,
I have a text file in this format :
02/24/2011                           Dynamic List Display                                   1
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
| ItemNum        |Plnt|SLoc|SL|BUn|QTY     |Trans./Tfr|Qual.Insp.|Restricted|Blocked|Returns|
---------------------------------------------------------------------------------------------
| 06326-50014-11 |5212|300A| |EA |      1 |        0 |        0 |        0 |     0 |     0 |
| 06650013-0132 |5212|300A| |EA |      1 |        0 |        0 |        0 |     0 |     0 |
| 06650233-0202 |5212|300A| |EA |      2 |        0 |        0 |        0 |     0 |     0 |
| 200-45394-0021 |5212|300A| |EA |      1 |        0 |        0 |        0 |     0 |     0 |
---------------------------------------------------------------------------------------------
|*               |    |    | |EA |    273 |          |          |          |       |       |
---------------------------------------------------------------------------------------------

can i convert this values to sql server compact database in PC?
i wan the itemnumber and the QTY to be converted to sdf(sqlserver database).

Is this possible?
If yes,how may i do this?

or in second case;
i have a sdf(sql server compact database).i wan to compare the item number in the database with the item number in above text file.if match found,it should list the itemnumber in another report says that itemnumber found.and vice-versa.
is it possible to do comparison between textfile and database,or we need to convert the textfile first to database before we can do the comparison?

which is possible?

any ideas guys?

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Guest User15y ago
Jerome's code (or a customized version of it that matches your text file's format) would work.

You can also run the command-line tool "bcp.exe" (assuming that's included with SQL Server compact edition) to accomplish the same goal.
jerome singh15y ago
u can try this store procedure.
but u need to have different between row and column. U can use delimitor for seperate column and row.

In this code for column tab is used, for row "|" is used.

CREATE PROCEDURE bulk_insert
@table_name varchar(250),
@input_file varchar(550),
@database_name varchar(50) ='cognos_customers_load'

SELECT @table_name = RTRIM(@table_name)
SELECT @input_file = RTRIM(@input_file)
SELECT @database_name = RTRIM(@database_name)

EXECUTE ( 'SET NOCOUNT ON

BULK INSERT ' + @database_name + '.dbo.' + @table_name +'
FROM ''' + @input_file + '''
WITH (
BATCHSIZE = 1000000
,DATAFILETYPE = ''char''
,FIELDTERMINATOR = ''\t''
,ROWTERMINATOR = ''|'' )

)
sam15y ago
how to do that?
Guest User15y ago
Sorry for the multiple posts. I'm having some browser issues.
Guest User15y ago
I would try to use the bcp (bulk copy) tool for something like this, but I don't know offhand if MS includes it in the compact version of SQL Server.