Page 1 of 1

[Solved] Importing from Calc CSV file

Posted: Tue Nov 29, 2022 5:05 pm
by georgegraz
I am trying to import From Calc.

Filename                     Fullname                        DateCreated         Filesize
2021-10-12 Central Ave.dwg   J:\2021-10-12 Central Ave.dwg   8/18/2016 03:27     12345678

I can manually enter this data into the table. But if I copy - paste, I get errors. 1 of them is S1000 Generalerror java.lang.nullpointerexception in statement. Continue anyways? I've tried creating different formats. no luck. Filename & fullname can start with letters or numbers. This is my 1st time using the programs.
Thanks for the help

 Edit: Added formatting tags -- MrProgrammer, forum moderator  

Re: Importing from Calc CSV file

Posted: Tue Nov 29, 2022 5:42 pm
by FJCC
The dwg file extension is not typical of a CSV file. Are you sure it is a CSV? How exactly are you using Copy - Paste to import the data?

Re: Importing from Calc CSV file

Posted: Tue Nov 29, 2022 6:21 pm
by georgegraz
that is the filename. I have a number of USB hard Drives & Memory sticks. With a program, it gives me a csv file of all the files on that Drive or stick. So my header is
Filename Fullname Date Created Filesize

Filename: 2021-10-12 Central Ave.dwg the filename is any Legal windows filename, so it can start with a letter or number.
Fullname: J:\2021-10-12 Central Ave.dwg This is the Filename & folder - location
Date Created format: 8/8/2016 03:27 This is the format for the date & time
Filesize: 1234567 this is the size of the file. Because will will surpass the limit of excel, I can't store all the info in Excel.
This is so, if I'm looking for a specific file, I can search for it, then plug in that particular stick or hard drive.
Thanks

Re: Importing from Calc CSV file

Posted: Tue Nov 29, 2022 7:09 pm
by georgegraz
I tried using Excel. After a few 4 gig Hard Drives, you could have over 1 million files. So, I wanted to take all the csv files ( 1 for each USB stick or hard drive) info & combine them into a searchable database. Thats what I'm trying to do. every record is the same format, Filename, Fullname, Datecreated,filesize. I'm doing it the way I found how to's. Last way I tried it was to declare fullname & filename as a Memo [lonvarchar]. It still shows up an error when trying to import them. I will try the "search inside". Thanks for the responses so far.

Re: Importing from Calc CSV file

Posted: Tue Nov 29, 2022 7:15 pm
by FJCC
Please post a small example file of a csv. It just needs to have a few rows of data.

Re: Importing from Calc CSV file

Posted: Tue Nov 29, 2022 8:03 pm
by georgegraz
Added a file. I think. Thanks

Re: Importing from Calc CSV file

Posted: Tue Nov 29, 2022 11:20 pm
by FJCC
Here is how I would connect to that file.
Save the file in a folder containing no other files
1. Add column names to the file
2. Start a new database file with File -> New -> Database
3. Select Connect To An Existing Database
4. Choose Text from the drop down list
5.Click Next
6.Browse to the folder containing the file. In this step you choose the folder, not the file.
7. Specify the type of file you want to access as Commas Separated Values file
8. Set the Field Separator to TAB
9. Click Next and accept defaults

You should now see one table in the database named after your file. You can add more data by appending to the existing file.

Re: Importing from Calc CSV file

Posted: Wed Nov 30, 2022 1:43 am
by John_Ha
Your csv file is pretty horrible as some gaps are spaces while others are tabs.

Recreating the csv file using fixed width fields may be easiest.

Re: Importing from Calc CSV file

Posted: Wed Nov 30, 2022 5:06 pm
by georgegraz
FJCC, that worked perfect!! I have like 50 files
USB001.csv
USB002.csv
etc.
I want to end up with 1 table. How do I now add the next one?
Thanks

Re: Importing from Calc CSV file

Posted: Wed Nov 30, 2022 7:19 pm
by georgegraz
Or, can each one have its own Table, then the Query will search all tables?
Thanks

Re: Importing from Calc CSV file

Posted: Wed Nov 30, 2022 7:21 pm
by Villeroy

Re: Importing from Calc CSV file

Posted: Wed Nov 30, 2022 7:45 pm
by Villeroy
Merging many text files into one is a trivial task on regular operating systems. On Windows you may try the "power shell": https://duckduckgo.com/?t=ffab&q=merge+ ... ell&ia=web

Re: Importing from Calc CSV file

Posted: Wed Nov 30, 2022 8:08 pm
by FJCC
@georgegraz
You can merge text files using the Command Prompt in Windows. Open the Command Prompt and navigate to the folder holding all your text files. Ask if you do not know how to do this. I assume none of the files have headers. Run this command

Code: Select all

copy /b *.csv AllDat.csv 
That will make a new file named AllDat.csv holding all of the data. Then move all of the individual csv files to another folder. You can now add a row of column headers to AllDat.csv

When you get new file named USB100.csv (again, I assume no headers) you can run

Code: Select all

copy /b AllDat.csv+USB100.csv AllDat.csv
to append the new data to AllDat.csv.

Re: Importing from Calc CSV file

Posted: Wed Nov 30, 2022 8:11 pm
by Villeroy
FJCC wrote: Wed Nov 30, 2022 8:08 pm copy /b *.csv AllDat.csv
This does not skip the header rows.

Re: Importing from Calc CSV file

Posted: Wed Nov 30, 2022 9:20 pm
by FJCC
Villeroy wrote: Wed Nov 30, 2022 8:11 pm This does not skip the header rows.
That is true. I believe the original files do not have headers.

Re: Importing from Calc CSV file

Posted: Wed Nov 30, 2022 10:00 pm
by georgegraz
They don't. I'm in the process of running a program that will tell me every file on the drive. Then adding a number associated with the drive. Then I will combine them as suggested.
Thanks for all the help.
George