[Solved] Adding Raw Data froma CSV file to an existing Table

[OnetimeAsker]

Good Morning.

I hate to make my first Thread a question, which in all honesty I didn't expend enough time searching for in the older threads, but I am actually pressed for time and would love some Help.

I am given a FIle in CSV format everyday with data on the stock of a company. This data is limited to just the code of the product and a number that represents the current Stock said company has (Code;Stock). Sadly this codes are rather aleatory, is not easy to guess what they reference by just looking at them, so I took the time to classify all 1000 and some manually into categories (I created a Spreadsheet on the base of the original file and added columns like "Maker", "category", "Product", and finally one to note if it had or not been added to the online store I am working on. With this classifications it's easier to work with them, since I can just organize the list and start working with Products I already have a good grasp with (This is a long time project that's just starting, and the original list had no order).

The problem is that I keep getting the CSV file with the " Code;Stock" format daily and I want to be able to update the stock in my new organized spreadsheet, but since there is no "0" stock in the lists, It either is there or there is none of it, I can't just Organize by code and copy the data.

I want to know if there is a way to use this new data to update the stock of the repeated codes, that I already got into my Spreadsheet, and add the extra ones below the estandar list, or wherever. It's not a big deal if stuff with no stock is still on the list (I can sort that out later) but I can't be missing products, or at least I don't want to.

I know it might be asking to much to have such a particular functions, but I thought It shouldn't be that hard (it's just about recognizing the elements on one column and updating the numbers on another), and I might be lucky enough that It is possible.

I am really looking forward to an answer, so thanks to however went to the trouble of reading all of this and Is thinking about it

Please, let me know if I wasn't clear enough, if I should add some sort of example, or if I made some sort of mistake while explaining (English is not my first Language)

[Villeroy]

csv is a database exchange format. So you want to append csv records to a database table?
1. Connect a Base document to a directory where you store all the equally structured csv files.
2. Open your database document which is connected to your database and the csv connected database document.
3. Drag the csv table from the second database window onto the table icon in the other database window.
4. A dialog pops up where you can map the incoming fields to the right fields in the target table.
Depending on your type of database, you may have other options.

For spreadheets and text documents:
1. Connect a Base document to a directory where you store all the equally structured csv files and make sure that the "register" option is checked.
2. In Calc or Writer hit F4 and drag whatever you need into the document.

[OnetimeAsker]

Thank you for answering Villeroy

I have Libre Office 5.2.2 on Windows 7, like on my sig. By pressing f4 i do nothing, and I am really not getting any of this at all. Not evena bit.

For starters I don't get how I am to connect the Base documente to a directory. I looked it up and ended up on a tutorial you made, but I was as Lost as before while looking at it. I couldn't find any options I am familiar with, and I am not even quite sure I was looking in the right direction either.

Where should I start reading? (If this information is anywhere)

I have no problem on investing my free time on learning how to do this properly, or even if I have to remake the table I made manually once or twice if on the long run I can make it work so that anybody can use it, but right now I don't even know where to start.

[Villeroy]

Oh, I forgot. LibreOffice makes chaotical changes to all of its menues and shortcuts. In Calc they changed it to Ctrl+Shift+F4 in order to adjust F4 to the Excel shortcut "toggle absolute/relative/mixed refs" which used to be Shift+F4 and just fine. But in Writer they kept the F4 shortcut.
Anyhow, in Calc it is menu:View>Data Sources

Store csv files with same file name suffix, same encoding and delimiters in a dedicated directory.
File>New>Database...
[X] Connect to existing database
Type: Text
Specify the dedicated directory, the encoding and delimiters.
[X] Yes, register the database
Save and close the database document and forget it. It is just a configuration file with many useful options you don't need yet. Your data are still in the text files.

Drag the table icon from the left part of the data source window into a document. In Calc you get a refreshable import range. When the csv file changed, click one cell in the range and call menu:Data>Refresh

[Villeroy]

If it is always the same file you want to link to always the same spreadsheet:
Open your Calc document (*.ods)
menu:Sheets>Insert Sheet From File...
Point to the csv file and check the link option.
Specify the right parameters, the "special numbers" option in particular.
To update the file link call menu:Edit>Links...

[OnetimeAsker]

Thanks a lot for taking the time to explain.

I am still working it up, but once I get it done I will be sure to add the "[SOLVED]" tag.

Danke.

[OnetimeAsker]

Ok, so I just checked and I don't think this is useful to me.

Could it be that with this option I get the same table as If I just open the CSV with Calc?

I don't need the table on its raw, two column, format. The problem is that I used one day of the daily data I am receiving to make a more complex table, manually, and I want to know if I can update the data on this new table with a the data on the CSVs. be it on a Spreadsheet or on a text document the code;stock format doesn't help me, since the product code does not tell me much (some older codes are mixed). I made a table to be able to organize by category, but I am unable to update such table, with out spending a considerable amount of time.

I tried the second method and it just deletes everything on the file and simply pastes the new data from the CVS document, while the first one doesn't come out looking well and has the same issue of just pasting the raw data on two columns.

To be more clear I started with this;

Code / Stock
5778 / 1

went to this;

Code / Type / Item / Label / Stock
5778 / Suspension / Shock Absorber / Nakata / 1

But still receive daily a list like

5779/1

And I want to know If I can just update the stock from the data without having to go item by item to control it, since there are over 1734 items, and several categories.

I can't just paste the information, since 0 stocks simply doesn't show on the CSV

Am I doing it wrong, or is simply imposible to change information selectively?

[Villeroy]

Please post a few lines of example data from the csv files to be imported.

[OnetimeAsker]

I don't know if this is going to be useful for you.

In the example the basic table is the on I get everyday on CVS format, while the other is what I want to make out of it.

I would rather not just upload the files to the Cloud, as I am supposed not to share them, but If necessary I would do it.

Again, thank you for taking the time to pay attention to my problem.

[Villeroy]

Nobody can work with screenshots.
I quit this topic.

[OnetimeAsker]

Wow, never thought it would get that much of a reaction.

Sorry, I really didn't think there was much to It. I did believe it might simply not be possible with out external software, hence why I thought a simple image would be enough to show what I was trying to convey


I am actually legally bound not to share the file, and I would rather it wasn't on the cloud ( There is no real problem with sharing it here particularly, since my country is not even an English speaking one).

I get numbers on CVS;
[Example]
Código;Stock
------;-----
228006;4
801408;3
003347;91
003431;10
6804;12
650392;64
801080;4
650344;1
650310;65
801292;2
801039;59
801171;27
801256;1
801414;1
006785;2
801416;2
650179;98
801222;1
227999;45
650336;5

I made it into a Table, then added more data (In this case Maker and Type of product)

I keep getting the data on the same format (numbers on CVS), and I wanted to know if it was possible to automatically update the modified table with out losing the added data, or if it simply was not possible.

I am really sorry If I somehow offended you, It was not my intention.

[Villeroy]

[Tutorial] VLOOKUP questions and answers which will give you an unmaintainable mess since spreadsheets are not intended to be used for accounting, book keeping or inventories of any kind.

[jrkrideau]

Wow, never thought it would get that much of a reaction.

Sorry, I really didn't think there was much to It. I did believe it might simply not be possible with out external software, hence why I thought a simple image would be enough to show what I was trying to convey

Lots of people do but about 90% of the the time the actual file or example file is needed. There probably not much to it with the right tools but using a spreadsheet for this is like using a screwdriver to pound in nails

I keep getting the data on the same format (numbers on CVS), and I wanted to know if it was possible to automatically update the modified table with out losing the added data, or if it simply was not possible.

I am really sorry If I somehow offended you, It was not my intention.

Villeroy occasionally a bit acerbic, don't worry about it. He also is one of the real experts.

This looks like a classic case for a database. I'm not sure about what you mean by "automatically update the modified table and I am not really a data base user but if the data is in true CSV format--that is, exported from a database then I think you can set up two tables and produce a third with perhaps one command. And every time you get new data you just add the data to the appropriate original table.

Also for a case like this a data base is much more versatile. As a simple example you could do something like how many items X do we use on Mondays?

As Villeroy points out using a spreadsheet is pretty close to madness. It is terribly error-prone and provides no audit-trail if you need to track down a problem.

And, BTW, once get the basics set up you create a report form and run it rather than doing a manual table. Again a reduction in errors and if all goes well it saves you 3 or 4 hours a day for other work and a decent lunch.

I'd suggest finding some basic texts on databases and perhaps SQL and do some reading. You already have a database in Base.

Database design is not for the faint of heart but this case looks pretty simple, in fact simpler than trying to use a spreadsheet for something it was never meant to be used for. Your development time for a Calc solution is almost certainly going to take longer than a database.

[Villeroy]

Code: Select all

Código;Stock
------;-----
228006;4

Is that second line with dashes really in the text file to import? It makes everything more difficult.

[OnetimeAsker]

Hello, and thank you for coming back to the thread, even though I pissed you off, and for what I had been explained righfuly so.

The line is really there

The text is literally;

Código;Stock
------;-----
228006;4
801408;3
003347;91
003431;10
6804;12
650392;64
801080;4
650344;1
650310;65
801292;2


I will try to learn how to use Database, and see what I can get out of it, thanks a lot for taking the time to read through the thread.

For now I will add the Solved Tag, and then ask in a new thread later if I don't get how to work around the Database (I understand it is meant to be easy, but I have a feeling I will be able to mess it up).

[Villeroy]

Databases are difficult to create and easy to use. Spreadsheets are the other way round.