Convert csv to xlsx

[RAJESH1960]

Hello code experts,
I have this csv file which was converted from pdf. The columns are not in one order to get the data in a columnar view. It will take more than a hour to arrange each line in a particular format. JohnnyL's code has helped me a lot to get the columns in order in less than a second. I am facing one problem in one of the conversions. The code is not able to concatenate the Cheque No. to the Description in 3 different rows and in one of the lines it is not taking the amount. Hence the balances are not matching. I have colored the lines with errors. Need your expertise to correct the code. Thanks.
Conver CSV to XLSX.xlsm

 

[Alex Blakenburg]

As well as an explanation please provide a sample final output file.

PS please but a full stop at the start of this line
.Range("A1").CurrentRegion.Resize(1).Font.Bold = True

 

[RAJESH1960]

First of all, the date needs to be corrected in the code.

Alex. The code is giving the right result. As I told you earlier that the problem is in the dates. The voucher type is missing too.

 

[RAJESH1960]

Voucher type means if the Dr. column has value it is Payment else Receipt. In my raw data sheet I do not need Src Row & balance columns headings. As the next code I am going to add to your code is going to select the data from columns in this order from the Raw sheet.

4. Import Bank Single Entry 28.12.2021.xlsm
	A	B	C	D	E	F	G
1	Date	Type	No.	Description	Name	Debit	Credit
Raw

 

[RAJESH1960]

The name column to be inserted in the same cell and is the only column which will be empty.

 

[RAJESH1960]

This is the output of your code. Compare the placement of columns and headings with expected result.

Test 21.02 csv to slsx convertor - Copy.xlsm
	A	B	C	D	E	F
1	Src Row	Txn Date	Description	Dr Amount	Cr Amount	Balance
2	33	30-11-2021	0040008700003314:Int.Coll:0 1-11-2021 to 30-11-202 Txn No:S36717089	1,42,061.00		2,01,33,776.81
3	34	30-11-2021	NEFT_IN:CMS2263255755/0 029/ NEFT SAI PRIYADARSHINI PNB BANK Txn No:S30265027		7,857.00	1,99,91,715.81
Raw

 

[RAJESH1960]

N

It won't happen today though.

No problem. Take your own sweet little time. Please just don't leave me in the lurch.?

 

[RAJESH1960]

Have you tried GWteB's code here, to see if you can get a cleaner starting point ?
Extract data from .PDF

Alex, I tried GWteB's code again. This time I unlocked the file by printing it to pdf. No doubt the code is good, but it will not help me as I am getting most of the data of one row in multiple rows. The pdf file is like in the image.

Book1
	A	B	C	D	E	F	G	H
584	M930833	01-12-2020	BY CASH -413400	TRICHY,VAYALUR ROAD			14,250.00	20,365.59 Cr.
585	S60152786	01-12-2020	3911002100003983 To: 0040005500052250	-			1,600.00	6,115.59 Cr.
586	S59801663	01-12-2020	NEFT_OUT:PUNBH20336555	-		17,700.00		4,515.59 Cr.
587			110/Vaulten Data
588			Solu/HDFC0002778/5020000 0555009
589	S59683354	01-12-2020	UPI/033603775068/P2V/9980 661815@ybl/MURUGESH K	-			8,010.00	22,215.59 Cr.
590	S59117986	01-12-2020	UPI/033647103196/P2V/8867	-			555	14,205.59 Cr.
591			443318@ybl/PARVEEZ AHMED
592	S58619840	01-12-2020	UPI/033637165261/P2V/6366	-			1,160.00	13,650.59 Cr.
593			070245@ybl/HITESH
594			GOWDA
Sheet2

 

[RAJESH1960]

I think I will go with your code. It is more than what I asked for. The remaining part is not so tough to finish it manually. I really appreciate for your help. If only you can correct the dates, that is enough.

 

[Alex Blakenburg]

I will have a look this afternoon my time.
(I can't tell what time zone you are in)

Tell me or show me the steps you are going to do manually and I will try to incorporate them.
A sample final output after applying the additional steps would help.

 

[RAJESH1960]

I will have a look this afternoon my time.
(I can't tell what time zone you are in)

Tell me or show me the steps you are going to do manually and I will try to incorporate them.
A sample final output after applying the additional steps would help.

Did you check the dates error..? That is the only problem.