Avatar of GPSPOW
GPSPOW
Flag 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
Programming

Avatar of undefined
Last Comment
GPSPOW

8/22/2022 - Mon
aikimark

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.
GPSPOW

ASKER
It is the last line after the detail.

Would -CACP work instead of -c?

thanks

glen
aikimark

Would -CACP work instead of -c?
I doubt it.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
aikimark

Are you sure you wanted to close this question?  There might be some work-arounds using VBScript or powershell or other scripts.
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
aikimark

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)?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
aikimark

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

ASKER
Yes
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
aikimark

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

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?
aikimark

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
aikimark

What is the row delimiter?
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
aikimark

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

ASKER
Where do I look for this information in the SQL 2008 server?
aikimark

you don't.  you look at the file
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
aikimark

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.
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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
aikimark

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

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
aikimark

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
aikimark

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

ASKER
will this effect it producing as a CSV, ANSI formatted with pipe delimiters?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
aikimark

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
GPSPOW

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

thanks

Glen