473,327 Members | 2,090 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Create table from Text

Hi, all. I'm fairly new to SQL, and I have been trying to create a table
from a text file. I have been looking at this for days, and can't find the
problem. I get a syntax error " Line 55: Incorrect syntax near
'DateUpdated'." Here is the query. Any suggestions would be appreciated,
as I am trying to learn and improve.

Use ACH
go

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ImportFiles]') and OBJECTPROPERTY(id, N'IsProcedure') =
1)
drop procedure [dbo].[ImportFiles]
GO

CREATE Procedure ImportFiles
@FilePath varchar(1000),
@MergeProc varchar(128) = 'MergeData'
AS
DECLARE @cmd varchar(2000),
@Command_String varchar(3000)

DECLARE @FileName varchar(1000),
@File varchar(1000)

CREATE table ##Import (datarow varchar(200))
CREATE table #Dir (datarow varchar(200))

DROP TABLE ACHParticipants

select @cmd = 'dir /B' + @FilePath
delete #Dir
insert #Dir exec master..xp_cmdshell @cmd

delete #Dir where datarow is null or datarow like '%not found%'

while exists (select * from #Dir)

BEGIN
select @FileName = min(datarow) from #Dir
select @file= @FilePath + @FileName
select @cmd = 'bulk insert'
select @cmd = @cmd + ' ##Import'
select @cmd = @cmd + ' from'
select @cmd = @cmd + ' @File,'
select @cmd = @cmd + ' with (FIELDTERMINATOR=''\n'''
select @cmd = @cmd + ',ROWTERMINATOR = '':\n'')'

truncate table ##Import

-- import the data
exec (@cmd)

-- remove filename just imported
delete #Dir where datarow = @FileName

exec @MergeProc
END

drop table ##Import
drop table #Dir
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MergeData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MergeData]
GO

CREATE PROCEDURE MergeData
AS
CREATE table ACHParticipants
(RoutingNum varchar(9),
OfficeCode varchar(1),
ServicingFRBNum varchar(9),
RecordType varchar(1),
ChangeDate varchar(8),
NewRoutingNum varchar(9),
BankName varchar(36),
BankAddress varchar(36),
City varchar(20),
State varchar(2),
Zipcode varchar(10),
Phone varchar(14),
StatusCode varchar(1),
DataView varchar(1),
Filler varchar(5),
DateUpdated datetime)

INSERT INTO ACHParticipants
(Routing_Number
, Office_Code
, Servicing_FRB_Number
, Record_Type_Code
, Change_Date
, New_Routing_Number
, Customer_Name
, Address
, City
, State_Code
, Zipcode
, Telephone
, Institution_Status_Code
, Data_View_Code
, Filler
, DateUpdated)

SELECT Substring(DataRow,1,9) AS RoutingNum,
Substring(DataRow,10,1) AS OfficeCode,
Substring(DataRow,11,9) AS ServicingFRBNum,
Substring(DataRow,20,1) AS RecordType,
convert(datetime,Substring(DataRow,21,6)) AS ChangeDate,
Substring(DataRow,27,9) AS NewRoutingNum,
Substring(DataRow,36,36) AS BankName,
Substring(DataRow,72,36) AS BankAddress,
Substring(DataRow,108,20) AS City,
Substring(DataRow,128,2) AS State,
Substring(DataRow,130,5) + '-' + Substring(DataRow,135,4) AS Zipcode,
Substring(DataRow,139,3) + '-' + Substring(DataRow,142,3) + '-' +
Substring(DataRow,145,4) AS Phone,
Substring(DataRow,149,1) AS StatusCode,
Substring(DataRow,150,1) AS DataView,
Substring(DataRow,151,5) AS Filler
DateUpdated datetime AS DateUpdated
FROM ##Import
GO
Thanks,
Karen
Jul 23 '05 #1
2 5118
The error is probably because you are missing a comma after "AS
Filler". In general, you should avoid creating permanent tables from
within stored procedures, as it makes it very hard to control your data
model correctly, and if the proc is run multiple times you may have
problems.

A common approach is to create a permanent staging table (instead of
using a temporary one as you are), and bulk load your files into that.
A stored proc can then do the final INSERT into ACHParticipants, after
making any other data changes that might be needed.

You might also want to consider loading the data using bcp.exe instead
of BULK INSERT - it can often be easier to deal with file names etc.
outside the database, in a batch file or a script of some other sort.

Simon

Jul 23 '05 #2
Thank you, Simon. I have put the comma in, and I am still getting the
error. I am going to try setting up a staging table - thanks again for the
suggestion.

Thank you for the good advice.
"Simon Hayes" <sq*@hayes.ch> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
The error is probably because you are missing a comma after "AS
Filler". In general, you should avoid creating permanent tables from
within stored procedures, as it makes it very hard to control your data
model correctly, and if the proc is run multiple times you may have
problems.

A common approach is to create a permanent staging table (instead of
using a temporary one as you are), and bulk load your files into that.
A stored proc can then do the final INSERT into ACHParticipants, after
making any other data changes that might be needed.

You might also want to consider loading the data using bcp.exe instead
of BULK INSERT - it can often be easier to deal with file names etc.
outside the database, in a batch file or a script of some other sort.

Simon

Jul 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: michaaal | last post by:
I use this code to create a table dynamically... strSQL = "CREATE TABLE (FirstName TEXT(30), LastName TEXT(20));" conn.execute(strSQL) This will create two text columns. How can I create...
2
by: Nick | last post by:
Loop to create an array from a dynamic form. I'm having trouble with an application, and I'll try to explain it as clearly as possible: 1. I have a form with two fields, say Apples and...
9
by: Lauren Quantrell | last post by:
Is there a way to create a text file (such as a Windows Notepad file) by using a trigger on a table? What I want to do is to send a row of information to a table where the table: tblFileData has...
0
by: Patrick | last post by:
I'm working on a contact management application, and need a hand with one aspect... Here's what I want to create: ------------------------------------ A form split into two parts. There is a...
8
by: ASP Yaboh | last post by:
I have an ArrayList of data gathered from a database. I want to create a web page from this data by creating a <table>, each cell in each row displays the appropriate data. One of those cells in...
9
by: Peter | last post by:
Hello£¬everyone, My program will collect a testing machine's data ,save the data and deal with the data everyday. I want to use vb.net to create database, add and delete tables or modify the...
4
by: JohnnyDeep | last post by:
I am trying to create a store proc that contain a create index with the cluster option and I receive DB21034E The command was processed as an SQL statement because it was not a valid Command...
4
by: etuncer | last post by:
Hello All, I have Access 2003, and am trying to build a database for my small company. I want to be able to create a word document based on the data entered through a form. the real question is...
15
by: lxyone | last post by:
Using a flat file containing table names, fields, values whats the best way of creating html pages? I want control over the html pages ie 1. layout 2. what data to show 3. what controls to...
3
by: DeanL | last post by:
Hi guys, Does anyone know of a way to create multiple tables using information stored in one table? I have a table with 4 columns (TableName, ColumnName, DataType, DataSize) and wanted to know...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.