Load from a file to QSqlTableModel

[dd44]

Hi ,

In my example I use a model/view. My class is QSqlTableModel for the model and QTableView for the view.
I know save data to a file (.txt and even .csv if I wish).

void Data_Example::txtExport()
{
 
        QString fileName = QFileDialog::getSaveFileName(this,
            tr("Save"), "",
            tr("Save (*.txt);;All Files (*)"));
 
        if (fileName.isEmpty())
            return;
        else {
            QFile file(fileName);
            if (!file.open((QIODevice::WriteOnly) | QIODevice::Text))
            {
                QMessageBox::information(this, tr("Unable to open file"),
                    file.errorString());
                return;
            }
 
            QString data;
            data ="";
 
         for (int row = 0; row < model->rowCount(); ++row)
         {
           QSqlRecord record = model->record(row);
                   for (int field = 0; field < record.count(); ++field)
                   {
                       if(field != 0 && field !=4 )
                       {
                           if (field > 1) data += "\n";
 
               data += record.field(field).value().toString();
 
                       }
                   }
         }
 
         QTextStream output(&file);
         output.setCodec("UTF-8");
         output << data;
 
}

void Data_Example::txtExport()
{
 
        QString fileName = QFileDialog::getSaveFileName(this,
            tr("Save"), "",
            tr("Save (*.txt);;All Files (*)"));
 
        if (fileName.isEmpty())
            return;
        else {
            QFile file(fileName);
            if (!file.open((QIODevice::WriteOnly) | QIODevice::Text))
            {
                QMessageBox::information(this, tr("Unable to open file"),
                    file.errorString());
                return;
            }
 
            QString data;
            data ="";
 
         for (int row = 0; row < model->rowCount(); ++row)
         {
           QSqlRecord record = model->record(row);
                   for (int field = 0; field < record.count(); ++field)
                   {
                       if(field != 0 && field !=4 )
                       {
                           if (field > 1) data += "\n";
 
               data += record.field(field).value().toString();
 
                       }
                   }
         }
 
         QTextStream output(&file);
         output.setCodec("UTF-8");
         output << data;
 
}

To copy to clipboard, switch view to plain text mode 

And in my file (.txt) I have for instance:

Alicia
Tucker
21/09/1985

John
Smith
18/02/1964

Jessica
Houston
07/11/1993

Now, I search to load this file or an other file like this but I don't find solutions...
I start to write this:

void Data_Example::txtImport()
{

QString fileName = QFileDialog::getOpenFileName(this,
tr("Open My File"), "",
tr("My File (*.txt);;All Files (*)"));

if (fileName.isEmpty())
return;
else
{
QFile file(fileName);

if (!file.open(QIODevice::ReadOnly | QIODevice::Text))
{
QMessageBox::information(this, tr("Unable to open file"),
file.errorString());
return;
}

QTextStream input(&file);
QString line;
do
{
input >> data;
line = input.readLine();
} while(!line.isNull());
//file.close();


}
}

This part doesn't function because I don't have a loop which load data in my QslqTableModel.
If somebody know...

Thanks.

[Rhayader]

The following code is untested but it will give you the basics

QTextStream in(&file);
    in.setCodec("UTF-8");
    QString line=in.readLine();
    while (!line.isNull())
    {
 
        QSqlRecord rec=model->record();
// just guessing that you have an autoincrement field on column 0
        rec.setValue(0, QVariant()); 
        for (int field=1 ; field<rec.count() ; ++field)
        {
            if (field==4) continue; // was ignored on save
            rec.setValue(field, line); // line is a Qstring. maybe you should change it to something else
        }
        model->insertRecord(-1, rec);
        line = in.readLine();
    }
// what is the model's edit strategy? 
// if it is QSqlTableModel::OnManualSubmit
// call model->submitAll() and check for errors
   if (!model->submitAll())
        qDebug()<<"error: "<<model->lastError().text();

QTextStream in(&file);
    in.setCodec("UTF-8");
    QString line=in.readLine();
    while (!line.isNull())
    {
         
        QSqlRecord rec=model->record();
// just guessing that you have an autoincrement field on column 0
        rec.setValue(0, QVariant()); 
        for (int field=1 ; field<rec.count() ; ++field)
        {
            if (field==4) continue; // was ignored on save
            rec.setValue(field, line); // line is a Qstring. maybe you should change it to something else
        }
        model->insertRecord(-1, rec);
        line = in.readLine();
    }
// what is the model's edit strategy? 
// if it is QSqlTableModel::OnManualSubmit
// call model->submitAll() and check for errors
   if (!model->submitAll())
        qDebug()<<"error: "<<model->lastError().text();

To copy to clipboard, switch view to plain text mode 

[dd44]

Thank you for your answer

The code functions and compiles, however I have some problems with the display.
In my view, the table display for instance:

Surname Name Birthday
Alicia Alicia Alicia
Tucker Tucker Tucker
21/09/1985 21/09/1985 21/09/1985

and when I click on it, I have "Alicia" in all my QLineEdit or "Tucker", or "21/09/1985"

So, I search to do this:

Surname Name Birthaday
Alicia Tucker 21/09/1985
John Smith 18/02/1964
Jessica Houston 07/11/1993

But I don't find...

[Rhayader]

From the output the error is quite obvious.

for (int field=1 ; field<rec.count() ; ++field)
        {
            if (field==4) continue; // was ignored on save
            rec.setValue(field, line); 
            line = in.readLine(); // one field per line
        }
        model->insertRecord(-1, rec);
        // line = in.readLine() this line moved inside the for  loop;

for (int field=1 ; field<rec.count() ; ++field)
        {
            if (field==4) continue; // was ignored on save
            rec.setValue(field, line); 
            line = in.readLine(); // one field per line
        }
        model->insertRecord(-1, rec);
        // line = in.readLine() this line moved inside the for  loop;

To copy to clipboard, switch view to plain text mode 

The code functions and compiles, however ...

Just because it compiles doesn't mean its logic is right.
My suggestion is that you should have tried a few alternatives yourself. After all these errors are quite common in alpha testing.

[dd44]

Ok thank you , I'm not very at ease with QSqlTableModel, so your help is important to me.
My problem is not completely solved because I can't read my file as I want.
Indeed, It works when the file is like this for example:

Alicia
Tucker
21/09/1985
John
Smith
18/02/1964
Jessica
Houston
07/11/1993

but if the file is like that:

Alicia
Tucker
21/09/1985

John
Smith
18/02/1964

Jessica
Houston
07/11/1993

It skips each time a cell in my view (so in my model too).

I don't find a method() or anything else to do this.

---

Added after 8 minutes:

Ok, I find the solution sorry

for (int field=1 ; field<rec.count() ; ++field)
{
if (field==4) continue; // was ignored on save
rec.setValue(field, line);
line = in.readLine(); // one field per line
}
model->insertRecord(-1, rec);
line = in.readLine();

For the moment it's OK for me...

Thks.