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

Question

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 yeshow 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 foundit should list the itemnumber in another report says that itemnumber found.and vice-versa. is it possible to do comparison between textfile and databaseor we need to convert the textfile first to database before we can do the comparison? which is possible? any ideas guys?

John Penn · Answer

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 singh · Answer

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 = ''	'' ROWTERMINATOR = ''|'' ) )

sam · Answer

how to do that?

John Penn · Answer

Sorry for the multiple posts. I'm having some browser issues.

John Penn · Answer

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.