Fixing database

Discuss the spreadsheet application

Fixing database

Postby kanuski » Sat Sep 15, 2018 10:57 pm

Greetings, I have, or had, a 1100 item database that was corrupted and can no longer be opened. I have it saved as a csv, semi-colon delineated and fields beginning and ending in quotation marks. Problem is that I also have many entries with semi-colons and quotation marks. So line breaks often don't work and many cells with multiple words are spread out over many cells with one word in each. I have it opened in Calc and am slowly fixing it.
E.g.
[Once] [upon] [a] [time] [Book]
I combine the first four cells and then un-combine them. Then delete the resulting spaces to move the 'book' into the second column. This is a short example. Some cells contain up to 15 words.
Question: is there a faster way, maybe using a macro, to speed up this process?

There are 13 columns in each row. When the line break didn't work there are often hundreds of columns. I have to scroll across, cut the 13 cells (more if they broke apart) I need and scroll back to the first column, create a new row and paste the results. Then scroll back and try to find the cells I need to cut and paste the next bunch.
Please tell me there are some shortcuts I can use for this.
Thanks for any help.
Apache OpenOffice 4.1.1
Windows 8.1
kanuski
 
Posts: 3
Joined: Thu Sep 14, 2017 2:49 pm

Re: fixing database

Postby kanuski » Sat Sep 15, 2018 11:13 pm

Just learned about Ctrl-Shift-Right to select everything to the right in the row. Very helpful.
Apache OpenOffice 4.1.1
Windows 8.1
kanuski
 
Posts: 3
Joined: Thu Sep 14, 2017 2:49 pm

Re: fixing database

Postby MrProgrammer » Sat Sep 15, 2018 11:50 pm

kanuski wrote:I have it saved as a csv, semi-colon delineated and fields beginning and ending in quotation marks.
Begin by reading [Tutorial] Text to Columns. If you need additional assistance attach the CSV data demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). The forum will not attach a CSV file so make a copy and change the extension from csv to txt. Are there supposed to be 13 fields in each line? Do not attach the spreadsheet after import. We need to see the source data.
Mr. Programmer
AOO 4.1.5 Build 9789 on Mac OS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3472
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 11 guests