SOLVED

Where is data hidden in Excel csv file

%3CLINGO-SUB%20id%3D%22lingo-sub-1408866%22%20slang%3D%22en-US%22%3EWhere%20is%20data%20hidden%20in%20Excel%20csv%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1408866%22%20slang%3D%22en-US%22%3E%3CP%3EOur%20vendor%20will%20send%20csv%20data%20files%20to%20us%20showing%20recent%20financial%20activity.%20They%20also%20send%20a%20pdf%20file%20the%20shows%20the%20actual%20date%20the%20funds%20are%20deposited%20to%20our%20bank.%20The%20dates%20on%20the%20csv%20do%20not%20always%20match%20the%20pdf%20because%20of%20the%20lag%20is%20posting%20from%20credit%20cards.%20Because%20of%20the%20differences%2C%20we%20need%20to%20cut%20some%20of%20the%20data%20from%20the%20original%20csv%20and%20paste%20it%20into%20another%20csv%20file.%20Here's%20the%20problem.%3C%2FP%3E%3CP%3EWhen%20we%20cut%20the%20data%20we%20also%20delete%20the%20entire%20row%2C%20however%20our%20financial%20database%20shows%20an%20error%20for%20each%20row%20deleted.%20We%20then%20go%20back%20and%20have%20to%20select%20ever%20row%20and%20every%20column%20with%20no%20data%20and%20delete%20them%20entirely.%20When%20we%20go%20to%20Info%20-%20Inspect%20Workbook%20nothing%20shows%20up.%20So%20where%20is%20the%20data%20that%20our%20database%20is%20finding%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1408866%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1408892%22%20slang%3D%22en-US%22%3ERe%3A%20Where%20is%20data%20hidden%20in%20Excel%20csv%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1408892%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F572680%22%20target%3D%22_blank%22%3E%40ParishAdmin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ecsv%20hides%20nothing%2C%20actually%20that's%20a%20text%20file.%20Why%20you%20system%20give%20an%20error%20that%20could%20be%20dozens%20of%20reasons%2C%20depends%20on%20which%20logic%20it%20uses.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%2C%20in%20a%20row%20in%20first%20column%20you%20have%20date%20and%20in%20second%20cell%20something%20like%203ABCxyz%20where%203%20says%20that%20next%20data%20shall%20be%20taken%20at%203%20rows%20down.%20Whatever%20else.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Our vendor will send csv data files to us showing recent financial activity. They also send a pdf file the shows the actual date the funds are deposited to our bank. The dates on the csv do not always match the pdf because of the lag is posting from credit cards. Because of the differences, we need to cut some of the data from the original csv and paste it into another csv file. Here's the problem.

When we cut the data we also delete the entire row, however our financial database shows an error for each row deleted. We then go back and have to select ever row and every column with no data and delete them entirely. When we go to Info - Inspect Workbook nothing shows up. So where is the data that our database is finding?

3 Replies
best response confirmed by ParishAdmin (Occasional Contributor)
Solution

@ParishAdmin 

csv hides nothing, actually that's a text file. Why you system give an error that could be dozens of reasons, depends on which logic it uses.

 

For example, in a row in first column you have date and in second cell something like 3ABCxyz where 3 says that next data shall be taken at 3 rows down. Whatever else.

I beg to differ. If I copy the contents of cell in a CSV file from one Excel spreadsheet to a new excel spreadsheet, and delete the cell contents I don't need, when I try to open the new CSV spreadsheet in our database, the db throws an error. The error lists how many instances in total, and the number by field of data that is incorrect.
I opened one of the original CSV files in Notepad and there is no data indicating row, column, field. See below.
Membernum,Ffundnumber,Fdate,Famount,Fcheck_number
33328562,1,12/07/2021,300.00,EFT
33328562,35,12/07/2021,100.00,EFT
33328562,57,12/07/2021,100.00,EFT
669,1,12/08/2021,180.00,EFT
669,278,12/08/2021,100.00,EFT
43262707,1,12/07/2021,400.00,EFT
33585272,1,12/08/2021,100.00,EFT
33261074,1,12/07/2021,175.00,EFT
33261074,278,12/07/2021,75.00,EFT

@ParishAdmin 

Sorry, I didn't catch. That's your text in Notepad

image.png

and same if open in Excel

image.png

Another story Excel could change formats, but you may correct that by importing to Excel using From Text wizard or Power Query.