gbucal01
asked on
Looking for a Windows Batch program to remove blank rows from a csv file
I have a csv file generated by a Crystal Reports Export where I need to remove the blank rows generated by the Export within Crystal. I am looking for a Windows batch program that can be executed at a command line to do that. The blank rows are strings of commas.
The csv file opened in a text editor looks like below (actual file has many more columns)...
"Header1","Header2","Heade r3"
"A1","B1","C1"
"A2","B2","C2"
,,,
"A3","B3","C3"
,,,
,,,
"A4","B4","C4"
I need the blank rows removed to generate...
"Header1","Header2","Heade r3"
"A1","B1","C1"
"A2","B2","C2"
"A3","B3","C3"
"A4","B4","C4"
The csv file opened in a text editor looks like below (actual file has many more columns)...
"Header1","Header2","Heade
"A1","B1","C1"
"A2","B2","C2"
,,,
"A3","B3","C3"
,,,
,,,
"A4","B4","C4"
I need the blank rows removed to generate...
"Header1","Header2","Heade
"A1","B1","C1"
"A2","B2","C2"
"A3","B3","C3"
"A4","B4","C4"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You are correct: /V is to output only lines that do not match the search string and "^,*$" is a regular expression which defines a string of commas. ^ means match at the beginning of the line ,* means match 1 or more commas and $ means match at the end of the line.
ASKER
Thank You for your help!
Dear All,
i am using oracle db for extract data i have try to extract data using batch files but out put is not match with my requirement i would like to share with you some example also submit the sql query with sql+ and batch file code
sql+ query
set linesize 10000
set headsep on
set colsep ','
SET UNDERLINE OFF
SET FEEDBACK OFF
SET NOCOUNT ON BEGIN
SET DOC_NO = LTRIM(RTRIM(DOC_NO))
spool &1
SELECT B.CUST_SID,CUST_ID,INVC_NO,TENDER_TYPE,COUNT(AMT) TTL_TRSNSCT,AMT,TO_CHAR(B.CREATED_DATE,'YYYYMMDD') AS INVC_DATE,concat(B.SBS_NO,B.STORE_NO) AS SBS_STR,TRIM(DOC_NO) AS GV_CODE FROM
(SELECT * FROM INVC_TENDER)A
LEFT JOIN INVOICE B ON A.INVC_SID=B.INVC_SID
LEFT JOIN CUSTOMER C ON B.CUST_SID=C.CUST_SID
WHERE B.CREATED_DATE BETWEEN to_date('10/06/2020:00:01:00',
'mm/dd/yyyy hh24:mi:ss') AND
to_date('10/06/2020:23:59:59', 'mm/dd/yyyy hh24:mi:ss')
AND B.CUST_SID IS NOT NULL and LENGTH(CUST_ID)='8'
GROUP BY B.CUST_SID,CUST_ID,INVC_NO,TENDER_TYPE,AMT,B.CREATED_DATE,B.SBS_NO,B.STORE_NO,DOC_NO;
spool off
exit;
batch file code:-SQLPLUS -s reportuser/googan@singh @H:\ERDAILY\DAILY_DATA_FILE\TENDERS.sql H:\ERDAILY\DAILY_DATA_FILE\tender_20201005_1315.CSV
current output headers reapt blank rows and white space in Columns name(GV_CODE)
I want to remove these above reapt headers,blank rows and space.
please help i have search many things but still want to get soluntion.
i am using oracle db for extract data i have try to extract data using batch files but out put is not match with my requirement i would like to share with you some example also submit the sql query with sql+ and batch file code
sql+ query
set linesize 10000
set headsep on
set colsep ','
SET UNDERLINE OFF
SET FEEDBACK OFF
SET NOCOUNT ON BEGIN
SET DOC_NO = LTRIM(RTRIM(DOC_NO))
spool &1
SELECT B.CUST_SID,CUST_ID,INVC_NO,TENDER_TYPE,COUNT(AMT) TTL_TRSNSCT,AMT,TO_CHAR(B.CREATED_DATE,'YYYYMMDD') AS INVC_DATE,concat(B.SBS_NO,B.STORE_NO) AS SBS_STR,TRIM(DOC_NO) AS GV_CODE FROM
(SELECT * FROM INVC_TENDER)A
LEFT JOIN INVOICE B ON A.INVC_SID=B.INVC_SID
LEFT JOIN CUSTOMER C ON B.CUST_SID=C.CUST_SID
WHERE B.CREATED_DATE BETWEEN to_date('10/06/2020:00:01:00',
'mm/dd/yyyy hh24:mi:ss') AND
to_date('10/06/2020:23:59:59', 'mm/dd/yyyy hh24:mi:ss')
AND B.CUST_SID IS NOT NULL and LENGTH(CUST_ID)='8'
GROUP BY B.CUST_SID,CUST_ID,INVC_NO,TENDER_TYPE,AMT,B.CREATED_DATE,B.SBS_NO,B.STORE_NO,DOC_NO;
spool off
exit;
batch file code:-SQLPLUS -s reportuser/googan@singh @H:\ERDAILY\DAILY_DATA_FILE\TENDERS.sql H:\ERDAILY\DAILY_DATA_FILE\tender_20201005_1315.CSV
current output headers reapt blank rows and white space in Columns name(GV_CODE)
I want to remove these above reapt headers,blank rows and space.
CUST_SID | TENDER_TYPE | TTL_TRSNSCT | AMT | INVC_DAT | SBS_STR | GV_CODE |
2.06E+18 | 2 | 1 | 100 | 20201006 | 227 | |
-1.34E+16 | 2 | 1 | 1455 | 20201006 | 2101 | |
1.25E+18 | 2 | 1 | 205 | 20201006 | 227 | |
5.69E+18 | 0 | 1 | 3199 | 20201006 | 2101 | |
1.81E+18 | 2 | 1 | 1774 | 20201006 | 28 | |
2.24E+18 | 2 | 1 | 656 | 20201006 | 2101 | |
1.51E+18 | 0 | 1 | -57 | 20201006 | 223 | |
2.15E+18 | 0 | 1 | 115 | 20201006 | 26 | |
2.72E+18 | 0 | 1 | 516 | 20201006 | 214 | |
2.11E+18 | 2 | 1 | 388 | 20201006 | 2101 | |
5.08E+18 | 2 | 1 | 1848 | 20201006 | 231 | |
2.18E+18 | 2 | 1 | 322 | 20201006 | 2101 | |
CUST_SID | TENDER_TYPE | TTL_TRSNSCT | AMT | INVC_DAT | SBS_STR | GV_CODE |
3.05E+18 | 0 | 1 | 149 | 20201006 | 1042 | |
1.59E+18 | 0 | 1 | -208 | 20201006 | 1045 | |
2.27E+18 | 2 | 1 | 345 | 20201006 | 1043 | |
5.51E+18 | 0 | 1 | 169 | 20201006 | 1045 | |
2.95E+18 | 2 | 1 | 2330 | 20201006 | 1047 | |
6.30E+17 | 2 | 1 | 145 | 20201006 | 1041 | |
3.31E+18 | 2 | 1 | 129 | 20201006 | 1049 | |
2.22E+18 | 2 | 1 | 30 | 20201006 | 1043 | |
5.70E+18 | 2 | 1 | 556 | 20201006 | 1043 | |
1.47E+17 | 2 | 1 | 1840 | 20201006 | 23 | |
5.53E+18 | 2 | 1 | 275 | 20201006 | 223 | |
2.05E+18 | 0 | 1 | 65 | 20201006 | 214 | |
please help i have search many things but still want to get soluntion.
ASKER