Link to home
Start Free TrialLog in
Avatar of GPSPOW
GPSPOWFlag for United States of America

asked on

How to eliminate null record at end of ANSI file

I am creating an ANSI formatted, PIPE delimited file using a batch file with BCP commands.  At the end of my files is a null record.  The vendor I am supplying these files to wants the NULL record eliminated.  Here is an example of data output and the Batch file coding.

Can someone tell me if there is a BCP switch that will do what I need?

SubFac|AccountNumber|AcctType|TxnAmount|PostDate|ServDate|Adm_Date|Disch_Date|TxnCode|TechDescr|FinClass|TxnInsCode|TotCharges|UnitNumb|UnitDate|PtTypeCode|ServType
P|A00000226880|FB|-4268.74|05/16/2014|05/16/2014|02/27/2014|03/17/2014|PTXHRS.65|PMT TEXAS HEALTH SPRING|MCR HMO|TXHRS.65|60372.04| | |I INP|MED
P|A00000105879|BD|-25.00|05/16/2014|05/16/2014|04/14/2011|04/18/2011|PFCA|FCA STATEMENT PAYMENTS|SP|SP|23766.94| | |I INP|MED



net use P: \\pmcfs\groups

bcp "select * from livedb.dbo.tbl_RCA_DAILY_TXN_HEADERS" queryout "\\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_HEADERS.csv" -T -SPTM-DR01 -c -t^|


bcp "select * from livedb.dbo.tbl_RCA_DAILY_TXN_DETAIL" queryout "\\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_Temp.csv" -T -SPTM-DR01 -c -t^|

copy \\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_HEADERS.csv + \\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_Temp.csv  "\\pmcfs\groups\ACCOUNTING\CHI\DailyTxnData\rca_p1_mt_daily_txn_"%date:~10,4%%date:~4,2%%date:~7,2%".csv"

net use P: /DELETE

Open in new window


Thanks

Glen
Avatar of aikimark
aikimark
Flag of United States of America image

Is the problem with the null record between the header and the detail rows or after the detail rows or both?

I don't think there is a switch on either the bcp or the copy commands that will do what you want.
Avatar of GPSPOW

ASKER

It is the last line after the detail.

Would -CACP work instead of -c?

thanks

glen
Would -CACP work instead of -c?
I doubt it.
Are you sure you wanted to close this question?  There might be some work-arounds using VBScript or powershell or other scripts.
Avatar of GPSPOW

ASKER

Here is the table structure in SQL I am trying to output to an ANSI file:

SubFac      varchar(255)      Checked
AccountNumber      varchar(255)      Checked
AcctType      varchar(255)      Checked
TxnAmount      decimal(15, 2)      Checked
PostDate      varchar(255)      Checked
ServDate      varchar(255)      Checked
Adm_Date      varchar(255)      Checked
Disch_Date      varchar(255)      Checked
TxnCode      varchar(255)      Checked
TechDescr      varchar(255)      Checked
FinClass      varchar(255)      Checked
TxnInsCode      varchar(255)      Checked
TotCharges      decimal(15, 2)      Checked
UnitNumb      varchar(255)      Checked
UnitDate      varchar(255)      Checked
PtTypeCode      varchar(255)      Checked
ServType      varchar(255)      Checked
            Unchecked

I know I cannot use the BCP commands from the SQL Server.  I do not have admin rights.  We also do not have the SSIS export command installed.

I am not familiar with VBScript nor PowerShell.  Is it envoked from a batch file?  If so what is the syntax and structure?  I will need it have the row headers as  the first line.

Thanks

glen
I know I cannot use the BCP commands

How can that be?  You posted a batch file with two bcp  commands.

How big are these extracted detail files (bytes and lines)?
Avatar of GPSPOW

ASKER

I am using a QueryOut command via a batch file.  I am not working from within a SQL Server Procedure.

There are 4 files I extract.  One runs about 8000 lines a day (3 MB), one can be up to 700 lines (100-300 Kb) and the other two 0-25 lines.
The first file has about 50 fields, the other three have the same structure as I posted above.

There are situations where I have to create tables with multiple years data and the output can reach as much as 70MB.

If I can get the smaller files working I can then apply the same formatting syntax procedures to the bigger file.

Thanks

Glen
Can you confirm that these files end with two pair of CrLf characters?
Avatar of GPSPOW

ASKER

Yes
This VBScript code will remove the last two bytes of your details file.
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fso, ts, strData

Set fso = CreateObject("Scripting.FileSystemObject")

Set ts = fso.OpenTextFile("\\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_Temp.csv", ForReading, False, TristateFalse)
strData = ts.ReadAll
ts.Close
strData = Left(strData, Len(strData) - 2)

' Open a text stream for input.
Set ts = fso.OpenTextFile("\\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_Temp.csv", ForWriting, False, TristateFalse)
ts.Write strData

ts.Close

Open in new window

Avatar of GPSPOW

ASKER

net use P: \\pmcfs\groups

bcp "select * from livedb.dbo.tbl_RCA_DAILY_TXN_HEADERS" queryout "\\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_HEADERS.csv" -T -SPTM-DR01 -c -t^|


bcp "select * from livedb.dbo.tbl_RCA_DAILY_TXN_DETAIL" queryout "\\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_Temp.csv" -T -SPTM-DR01 -c -t^|

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fso, ts, strData

Set fso = CreateObject("Scripting.FileSystemObject")

Set ts = fso.OpenTextFile("\\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_Temp.csv", ForReading, False, TristateFalse)
strData = ts.ReadAll
ts.Close
strData = Left(strData, Len(strData) - 2)

' Open a text stream for input.
Set ts = fso.OpenTextFile("\\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_Temp.csv", ForWriting, False, TristateFalse)
ts.Write strData

ts.Close


copy \\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_HEADERS.csv + \\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_Temp.csv  "\\pmcfs\groups\ACCOUNTING\CHI\DailyTxnData\rca_p1_mt_daily_txn_"%date:~10,4%%date:~4,2%%date:~7,2%".csv"

net use P: /DELETE

Open in new window


Would this be proper code to do what I want?
You will need to put the VBScript code in a .VBS file and then invoke that in/from your existing batch file (in the same place you currently have the VBScript statements.

Question: why are you mapping the P: drive?  You don't seem to be using it in your batch file.
Avatar of GPSPOW

ASKER

I love the VBS script.

However, I think I told you wrong before.

There are no CrLf at the end of the detail lines until the end.

What I am getting now is a CrLf in the last position of the last detail record.

Can that be eliminated?

Glen
What is the row delimiter?
Avatar of GPSPOW

ASKER

I do not see a row delimiter in the BCP.

bcp "select * from livedb.dbo.tbl_RCA_DAILY_TXN_HEADERS" queryout "\\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_HEADERS.csv" -T -SPTM-DR01 -c -t^|


bcp "select * from livedb.dbo.tbl_RCA_DAILY_TXN_DETAIL" queryout "\\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_Temp.csv" -T -SPTM-DR01 -c -t^|

Glen
look at your actual data
What character(s) delimit each row?
Avatar of GPSPOW

ASKER

Where do I look for this information in the SQL 2008 server?
you don't.  you look at the file
Avatar of GPSPOW

ASKER

Here are the two CSV files created by the BCP.  I do not see a character delimiting the row.

I appreciate your help.

Glen
RCA-DAILY-TXN-Temp.csv
RCA-DAILY-TXN-HEADERS.csv
The rows are delimited with CrLf characters.
The last row of the detail file is not delimited.

Based on what you've written and what I've observed in your files, I no longer understand your problem.
Avatar of GPSPOW

ASKER

P|A00000235698|UB|-900.00|05/21/2014|05/21/2014|05/21/2014|05/21/2014|PSP CC|PAYMENT SELF PAY CREDIT CARD|SP|SP|| | |O SDC|OR
P|A00000236230|UB|-400.00|05/21/2014|05/21/2014|05/21/2014|05/21/2014|PSP CASH|PAYMENT SELF PAY CASH|SP|SP|| | |O SDC|OR
P|A00000236296|UB|-250.00|05/21/2014|05/21/2014|05/21/2014|05/21/2014|PSP CC|PAYMENT SELF PAY CREDIT CARD|SP|SP|| | |O OUT|MRI

When I look at the above with Notepad++, everything is normal for the first two records.  There is a CrLf at the end of each line.

The third line has a special character at the end (looks like a little box).  When viewed with Notepad++  it shows "SUB".  I need to eliminate this from the last line and only have a CrLf at the end.

Thanks

Glen
Change the third line in the sequence that begins with the ReadAll method.
strData = ts.ReadAll
ts.Close
strData = Replace(strData, Chr(26), vbNullString)

Open in new window

Avatar of GPSPOW

ASKER

Okay,  I am still getting the special character at the end of the file.

Here is my Ansi,vbs code:

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fso, ts, strData

Set fso = CreateObject("Scripting.FileSystemObject")

Set ts = fso.OpenTextFile("\\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_Temp.csv", ForReading, False, TristateFalse)
strData = ts.ReadAll
ts.Close
strData = Replace(strData, Chr(26), vbNullString)
' Open a text stream for input.
Set ts = fso.OpenTextFile("\\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_Temp.csv", ForWriting, False, TristateFalse)
ts.Write strData

ts.Close

Open in new window


Here is the batch file that incorpoarate the above code when transforming the 'csv' file:

net use P: \\pmcfs\groups

bcp "select * from livedb.dbo.tbl_RCA_DAILY_TXN_HEADERS" queryout "\\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_HEADERS.csv" -T -SPTM-DR01 -c -t^|


bcp "select * from livedb.dbo.tbl_RCA_DAILY_TXN_DETAIL" queryout "\\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_Temp.csv" -T -SPTM-DR01 -c -t^|

cscript.exe C:\Tasks\Ansi.vbs

copy \\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_HEADERS.csv + \\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_Temp.csv  "\\pmcfs\groups\ACCOUNTING\CHI\DailyTxnData\rca_p1_mt_daily_txn_"%date:~10,4%%date:~4,2%%date:~7,2%".csv"

net use P: /DELETE

Open in new window


I am attaching the output file.  You will see the special character on the last line of the file.

I just need to eliminate this line or character.

Thanks

Glen
rca-p1-mt-daily-txn-20140522.csv
This version of the vbscript code will remove both the SUB and the immediately prior CrLf characters.
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fso, ts, strData

Set fso = CreateObject("Scripting.FileSystemObject")

Set ts = fso.OpenTextFile("\\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_Temp.csv", ForReading, False, TristateFalse)
strData = ts.ReadAll
ts.Close
strData = Replace(strData, vbcrlf & Chr(26), vbNullString)
' Open a text stream for input.
Set ts = fso.OpenTextFile("\\pmcfs\groups\ACCOUNTING\RCA_DAILY_TXN_Temp.csv", ForWriting, False, TristateFalse)
ts.Write strData

ts.Close

Open in new window

Avatar of GPSPOW

ASKER

I want to thank you staying with me on this.

It seems that the special character occurs when I invoke the Copy command to combine the  csv files into one data file.

I tried using the VB script on the both the 'temp' file and the combined file.

The 'temp' csv file does not have the special character.  This file was created by the BCP Sql output command.  The combined file is created by the COPY command.  I think this is the problem.

Any suggestions?

Thanks

glen
try using the switch on the Copy command that says to treat the file like a binary file instead of ASCII
Avatar of GPSPOW

ASKER

will this effect it producing as a CSV, ANSI formatted with pipe delimiters?
ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of GPSPOW

ASKER

I expiramented with different scenarios and finally got the NUL and SUB eliminated.

thanks

Glen