• Registered: 2016-01-17
  • Posts: 151

Topic: import problem

Hi every one
I have encountered a problem and its killing me.
I have searched through the forum looking for importing excel and I couldn't find any post that fits my purpose.
I have about 100 excel sheets that I need to import them into my project
I have attached the project and a sample excel file (all the Excel files are the same, only with different data)
I wonder whether it's possible to import these files into the forms of the project?
it's very important for me because I can't enter them manually (so help me please if you can)

Post's attachments

Attachment icon Drug.zip 518.93 kb, 101 downloads since 2024-05-07 

  • Registered: 2014-08-02
  • Posts: 960

Re: import problem

Hello identity
Can you tell us more
- Do you get error messages during import?
- Does this concern a problem with formats, import of cells with titles, date format, etc.?
- CSV format not compliant?
More information will help us help you
Sincerely
JB

  • Registered: 2016-01-17
  • Posts: 151

Re: import problem

thanks JB for the fast reply
I Don't know how to import excel in the first place so there is no error message.
excel sheets have titles the same as the textboxes in the project. I just need the data in the columns of the attached sample excel file to be imported into the project.
the data of the excels are only numbers (prices of medicines) as well as the name of the patient and type of medicine.
I tried to convert excel file to csv format but I couldn't so I only have Excel files

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,786

Re: import problem

Hi Identity, Salut Jean,
Before trying to import data, are you sure that your data schema is correct? 
I am surprised that somethings like 'unit price', 'currency', 'nongovprice' are held on the 'drug' table (ie specific to each individual patient) and not common to all (and therefore would typically be held on the 'medicinetype' table).  Also, why are 'firstname', 'lastname' and 'patientid' held on the 'drug' table when there is already a relationship bewtween the 'drug' and 'patient' tables?
In the sample Excel file you attached, there are repeated data rows (eg Albomin 50 appears 15 times).  Anomalies like this need to be corrected before you try to import your data or you will just be importing problems and creating extra re-work..
Also, there are things that appear unusual on your forms;
1. you use the 'patient' table on 'tablegrid3' on the 'searchfrm' and yet you include drug information on it 
2. on the 'patientinfo' form, 'tablegrid4' is using the 'patient' table - shouldn't this be the 'drug' table?
There may be other things - I didn't look any further.
Regards,
Derek.

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 1,077

Re: import problem

http://myvisualdatabase.com/forum/viewtopic.php?id=5143

http://myvisualdatabase.com/forum/viewtopic.php?id=5163

https://myvisualdatabase.com/forum/view … hp?id=6986

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 1,077

Re: import problem

In addition to the previous links, I would like to give a link to another project from "prahousefamily".
https://myvisualdatabase.com/forum/view … hp?id=5175

The project allows you to batch process files and display progress in a TableGrid. The only thing missing in this project is saving it in a SQLite database. But this can be fixed. You just need to add the command.


And I want to provide an updated project from "prahousefamily" with an optimized progressbar function (increased performance, improved display, eliminated flickering).

Post's attachments

Attachment icon GridTextBar-m.zip 537.24 kb, 107 downloads since 2024-05-07 

  • Registered: 2014-06-25
  • Posts: 874

Re: import problem

identity wrote:

I tried to convert excel file to csv format but I couldn't so I only have Excel files

I think, the very first step would be to get the CSV files: https://www.exceldemy.com/convert-excel … delimited/
Every version of MS Excel, libreoffice or OpenOffice, even Mac OS Numbers can do that.

Or you can go through the links provided by Sparrow but this is more advanced.

To import data:

You can create a button with no action on your form with an OnClick event like so:

procedure Form1_ButtonImportData_OnClick (Sender: TObject; var Cancel: boolean);
begin
Form1.mniImportData.Click;
end;

Or you can use SQLiteStudio to import the data directly into the tables of your choice, but this is working directly in the database.

I found out that using the button to import CSV data coming straight from Excel was difficult due to Excel using double quotes for data and that is trouble, you might have to edit the CSV files in notepad or notepad++ to remove all double quotes.

  • Registered: 2016-01-17
  • Posts: 151

Re: import problem

thank you guys
you helped me alot
I could finally converted excel to cvs and inserted the data into the project.
there is one slight problem though
in the search form , I used combobox to show first name and last name of a patient.
the patient use similar or sometimes different medicine everyday and that's why there is only one patient name and sometimes similar drugs in the form.
the problem is that the combobox in the search form shows repeated names?
is it possible that the repeated names in that combobox are not shown?
please forget about the schema of the attached project because its a sample one and it fits my purpose.

Post's attachments

Attachment icon Drug v1.zip 508.14 kb, 105 downloads since 2024-05-09 

  • Registered: 2014-06-25
  • Posts: 874

Re: import problem

You are using a database like an excel spreadsheet, this is not at all the way a database works, you should have a table for the patients containing first name, last name and other patient related information and they must be unique, they are identified by a unique ID. You should have a table containing the medication names with maybe details about the medication. You could also have a table for insurances. Then you use relationships to link the patient to the medicine and the insurance. Then you have a table that stores all these information together using ids. Have a look at the Myvisualdatabase inventory example.