Libre Office 6.0.5.2
When copying a little over 1700 lines from a Calc to Base, about 1/3 of the records are missing, in random numbers, not from one block of data. The total lines in the spreadsheet are 1707 and getting only 1209 records in Base.
Hello,
Can’t imagine how your question can be answered with the information presented. It is extremely generalized and there are many conditions which may affect the processing.
Just ran a test. Using LO v6.1.0.3 on Mint 18.3:
500+ lines in Calc
4 columns consisting of date, text, amount(10v2), text
Using default HSQL embedded in Base, created a table with an auto increment field for the key, then the other four fields to be transferred from Calc.
The Calc columns had the same Name at the top of each column to match the table names.
Copied all rows in Calc and pasted in Base table. This started a dialog. Insured table name was correct and Append data was selected. Clicked Next button. Insured fields matched up. Had to move fields because auto increment ID field is not in Calc file. Clicked Create button. Opened newly created table. A count of all records matched. Performed a sum of amounts in Calc file and SQL using sum in Base file. Totals matched.
You can see by my process it is more descriptive than what you have presented and did not experience any problem.
However, this is not to say you don’t have a problem but there is no way to help based upon the provided information.
If this process doesn’t work for you, please present the steps you are performing and more information about the DB used, fields, OS and whatever else may be relevant.
You neglected to tell me what you need.
This was stated in the last sentence of the answer.
As noted at the top, I’m using LO 6.0.5.2, so the behavior may be different than the version you’re working with. I was following the instructions listed in a Wiki, of which I no longer have the page reference. I can’t imagine what difference it would make, but my OS is Kubuntu 14.04. I had been attempting to use the “Definition & Data” selection in your aforementioned Dialog when pasting the Calc data into Base, rather than the “Append” selection. More to follow…
Since I was copying the entire contents of the spreadsheet, it made more sense to use the Definition & Data, rather than the Append selection. Using the method you outlined, it again lost about 300 records, as apposed to the 500 I got earlier. Therefore, still not working. It’s giving me a Warning as follows: Value too long in statement [INSERT INTO “Table3” (“ID”,"Artist…] naming each of the column fields, followed by VALUES (?,?,?,?,…) Continue Anyway? I’m stumped.
@DPNoyes Thank you for the additional information. It is key as to your problem which I can duplicate.
In my test, I simply made two of the table Varchar fields smaller than what it needed to be. Since the receiving field (in table) is smaller than what is being copied from the originating data (from Calc) the record is not transferred. All that is needed if to figure out which field is the culprit & adjust the table field size. This should correct the problem.
The most likely problem field is a text field exceeding 255 characters. In this case you need to make this a Memo field (LONGVARCHAR).
One of the missing records had 41 characters in one field,when I had specified in the Table Definition that this field should only have 40 (thought that would be enough), which was the largest field in this table. Could it be that when I copied the spreadsheet, Base ignored any field larger than that specified in the given field? If so, that may have been the root of the whole problem. I will try with larger field limits in the definition. More to follow…
2 more questions: What’s the difference between “Definition & Data” and “Append”? And, I may be operating from outdated information, but many years ago I learned that the field length reserved space for that many characters in the table, therefore making the table use more hard drive space, (space was precious back in those days). Is this no longer the case? This may be a moot question since these days we have vastly more storage space.
It would appear I simply underestimated the field lengths. After increasing the Field Lengths, I now have all records. I have 5 numeric fields and got an error message (a little vague) about that, but leaving those fields defined as text (should be limited to numerals), I got no further errors. Thanks for pointing me in the right direction.
@DPNoyes, Definition & Data will create a complete table from another source (internal or external) and the process defines the fields and appends the data. Append simple is adding data to an already defined table whether it has data in it or is just defined. Fields, depending upon type, will vary in length. You need to see the documentation of the database you are using to see specifically what is used for each type.
Based upon you last comment it appears you still have some type of problem. You should not need to set numeric fields as text (varchar). There is probably something different in either the input or still in the definition of the field types.
.The original problem was created by my underestimation of the field lengths required for my purposes, and the Error Message not being clear. If some records fail to appear when migrating from a spreadsheet, check your limits on Field Length. If the set length is less than the data in any given record, Base will delete that record from the new build.