Data format error внешняя таблица не имеет предполагаемый формат - Исправление ошибок и поиск оптимальных решений проблем

Community,

I am having a problem importing files from a sharepoint folder (6 files that need to be brought together). I see these as .xls files in Sharepoint and therefore use:

let
Source = Folder.Files(…. /sharepoint location»…….),

// no problem
#»Removed Other Columns» = Table.SelectColumns(Source,{«Content»}),
#»Added Custom» = Table.AddColumn(#»Removed Other Columns», «GetExcelData», each Excel.Workbook([Content])),

//no problem — I am able to see all the data in PowerQuery

Everything looks fine — ready to go.

However, when I hit «Close and Apply» — the system returns an error:

[DataFormat.Error] External table is not in the expected format.

The original files generated from a SQL query — and then the results of the query are pasted into an Excel file…. The orginal response that is being pasted is a tab delimited file — which may be causing the problem at some level.

Is there a different approach I should be using to open these files? Instead of Excel.Workbook([Content}) is there a something else that will be more appropriate for a tab delimited file?

Thanks in advance.

Источник

I’m trying to read an Excel (xlsx) file using the code shown below. I get an «External table is not in the expected format.» error unless I have the file already open in Excel. In other words, I have to open the file in Excel first before I can read if from my C# program. The xlsx file is on a share on our network. How can I read the file without having to open it first?
Thanks

string sql = "SELECT * FROM [Sheet1$]";
string excelConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathname + ";Extended Properties="Excel 8.0;HDR=YES;IMEX=1;"";

using (OleDbDataAdapter adaptor = new OleDbDataAdapter(sql, excelConnection)) {
    DataSet ds = new DataSet();
    adaptor.Fill(ds);
}

pnuts

57.8k11 gold badges85 silver badges137 bronze badges

asked Jul 16, 2009 at 18:23

«External table is not in the expected format.» typically occurs when trying to use an Excel 2007 file with a connection string that uses: Microsoft.Jet.OLEDB.4.0 and Extended Properties=Excel 8.0

Using the following connection string seems to fix most problems.

public static string path = @"C:srcRedirectApplicationRedirectApplication301s.xlsx";
public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

answered Sep 1, 2009 at 16:38

FAtBalloonFAtBalloon

4,4701 gold badge24 silver badges33 bronze badges

Thanks for this code I really appreciate it. Works for me.

public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

So if you have diff version of Excel file, get the file name, if its extension is .xlsx, use this:

Private Const connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

and if it is .xls, use:

Private Const connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" + path + ";Extended Properties=""Excel 8.0;HDR=YES;"""

answered Feb 11, 2010 at 6:38

TrexTrex

2893 silver badges2 bronze badges

(I have too low reputation to comment, but this is comment on JoshCaba’s entry, using the Ace-engine instead of Jet for Excel 2007)

If you don’t have Ace installed/registered on your machine, you can get it at: https://www.microsoft.com/en-US/download/details.aspx?id=13255

It applies for Excel 2010 as well.

Eunoseer

753 silver badges10 bronze badges

answered Feb 25, 2011 at 14:10

cederlofcederlof

7,0764 gold badges44 silver badges62 bronze badges

Just add my case. My xls file was created by a data export function from a website, the file extention is xls, it can be normally opened by MS Excel 2003. But both Microsoft.Jet.OLEDB.4.0 and Microsoft.ACE.OLEDB.12.0 got an «External table is not in the expected format» exception.

Finally, the problem is, just as the exception said, «it’s not in the expected format». Though it’s extention name is xls, but when I open it with a text editor, it is actually a well-formed html file, all data are in a <table>, each <tr> is a row and each <td> is a cell. Then I think I can parse it in a html way.

answered Jan 22, 2016 at 12:25

I had the same problem. which as resolved using these steps:

1.) Click File

2.) Select «save as»

3.) Click on drop down (Save as type)

4.) Select Excel 97-2003 Workbook

5.) Click on Save button

answered Mar 13, 2019 at 6:49

KamranKamran

6516 silver badges8 bronze badges

I had this same issue(Using the ACE.OLEDB) and what resolved it for me was this link:

http://support.microsoft.com/kb/2459087

The gist of it is that installing multiple office versions and various office sdk’s, assemblies, etc. had led to the ACEOleDB.dll reference in the registry pointing to the OFFICE12 folder instead of OFFICE14 in

C:Program FilesCommon FilesMicrosoft SharedOFFICE14ACEOLEDB.DLL

From the link:

Alternatively, you can modify the registry key changing the dll path to match that of your Access version.

Access 2007 should use OFFICE12, Access 2010 — OFFICE14 and Access
2013 — OFFICE15

(OS: 64bit Office: 64bit) or (OS: 32bit Office: 32bit)

Key: HKCRCLSID{3BE786A0-0366-4F5C-9434-25CF162E475E}InprocServer32

Value Name: (Default)

Value Data: C:Program FilesCommon FilesMicrosoft
SharedOFFICE14ACEOLEDB.DLL

(OS: 64bit Office: 32bit)

Key:
HKCRWow6432NodeCLSID{3BE786A0-0366-4F5C-9434-25CF162E475E}InprocServer32

Value Name: (Default)

Value Data: C:Program Files (x86)Common FilesMicrosoft
SharedOFFICE14ACEOLEDB.DLL

answered Jun 5, 2013 at 12:46

jordanhill123jordanhill123

4,1142 gold badges30 silver badges40 bronze badges

I have also seen this error when trying to use complex INDIRECT() formulas on the sheet that is being imported. I noticed this because this was the only difference between two workbooks where one was importing and the other wasn’t. Both were 2007+ .XLSX files, and the 12.0 engine was installed.

I confirmed this was the issue by:

Making a copy of the file (still had the issue, so it wasn’t some save-as difference)
Selecting all cells in the sheet with the Indirect formulas
Pasting as Values only

and the error disappeared.

answered May 11, 2012 at 19:33

I was getting errors with third party and Oledb reading of a XLSX workbook.
The issue appears to be a hidden worksheet that causes a error. Unhiding the worksheet enabled the workbook to import.

answered Jul 4, 2014 at 18:06

John MJohn M

14k29 gold badges89 silver badges139 bronze badges

If the file is read-only, just remove it and it should work again.

answered Feb 4, 2019 at 22:25

TehscriptTehscript

2,5662 gold badges12 silver badges22 bronze badges

I know this is a very old post, but I can give my contribution too, on how I managed to resolve this issue.

I also use «Microsoft.ACE.OLEDB.12.0» as a Provider.
When my code tried to read the XLSX file, it received the error «External table is not in the expected format.»
However, when I kept the file open in Excel and then the code tried to read it … it worked.

SOLUTION:
I use Office 365 with company documents and in my case, the solution was very simple, I just needed to disable the sensitivity of the document, setting it to «public».
Detail: Even after saving as «public» the green check still remained marked in «Internal Use», but the problem remained solved after that.

answered Nov 18, 2020 at 14:14

MMJMMJ

5264 silver badges6 bronze badges

I had this problem and changing Extended Properties to HTML Import fixed it as per this post by Marcus Miris:

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & importedFilePathAndName _
         & ";Extended Properties=""HTML Import;HDR=No;IMEX=1"";"

answered Jun 24, 2016 at 13:07

majjammajjam

1,2562 gold badges15 silver badges31 bronze badges

ACE has Superceded JET

Ace Supports all Previous versions of Office

This Code works well!

        OleDbConnection MyConnection;
        DataSet DtSet;
        OleDbDataAdapter MyCommand;
        
        MyConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\Book.xlsx;Extended Properties=Excel 12.0;");
        MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
        DtSet = new System.Data.DataSet();
        
        MyCommand.Fill(DtSet);
        dataGridView1.DataSource = DtSet.Tables[0];
        MyConnection.Close();

answered Oct 3, 2016 at 11:14

Ran into the same issue and found this thread. None of the suggestions above helped except for @Smith’s comment to the accepted answer on Apr 17 ’13.

The background of my issue is close enough to @zhiyazw’s — basically trying to set an exported Excel file (SSRS in my case) as the data source in the dtsx package. All I did, after some tinkering around, was renaming the worksheet. It doesn’t have to be lowercase as @Smith has suggested.

I suppose ACE OLEDB expects the Excel file to follow a certain XML structure but somehow Reporting Services is not aware of that.

answered Jun 27, 2018 at 8:02

kerweikerwei

1,8021 gold badge13 silver badges22 bronze badges

the file might be locked by another process, you need to copy it then load it as it says in this post

answered Feb 16, 2014 at 23:58

user3140982user3140982

1351 gold badge3 silver badges8 bronze badges

Just adding my solution to this issue. I was uploading a .xlsx file to the webserver, then reading from it and bulk inserting to SQL Server. Was getting this same error message, tried all the suggested answers but none worked. Eventually I saved the file as excel 97-2003 (.xls) which worked… only issue I have now is that the original file had 110,000+ rows.

answered Jun 17, 2015 at 9:47

Kieran QuinnKieran Quinn

1,0551 gold badge22 silver badges49 bronze badges

If you still have this problem, then check your permissions, I tried many of these suggestions and my concrete problem was that the file that I wanted to process was under source control and the thread had no permissions, I had to change the entire folder permissions and it started to work (I was processing many files in there)… It also matches many suggestions like change the name of the file or check that the file is not loicked by another process.

I hope it helps you.

answered Feb 16, 2016 at 6:32

JuanJuan

2,12817 silver badges26 bronze badges

answered Jul 16, 2009 at 18:33

NelsonNelson

4644 silver badges10 bronze badges

This can occur when the workbook is password-protected. There are some workarounds to remove this protection but most of the examples you’ll find online are outdated. Either way, the simple solution is to unprotect the workbook manually, otherwise use something like OpenXML to remove the protection programmatically.

answered Feb 2, 2017 at 15:25

KthProgKthProg

2,0001 gold badge23 silver badges32 bronze badges

I recently saw this error in a context that didn’t match any of the previously listed answers. It turned out to be a conflict with AutoVer. Workaround: temporarily disable AutoVer.

answered Apr 26, 2017 at 17:46

unbobunbob

3213 silver badges6 bronze badges

That excel file address may have an incorrect extension. You can change the extension from xls to xlsx or vice versa and try again.

answered Jun 27, 2018 at 8:25

MiMFaMiMFa

8449 silver badges13 bronze badges

I recently had this «System.Data.OleDb.OleDbException (0x80004005): External table is not in the expected format.» error occur. I was relying on Microsoft Access 2010 Runtime. Prior to the update that was automatically installed on my server on December 12th 2018 my C# code ran fine using Microsoft.ACE.OLEDB.12.0 provider. After the update from December 12th 2018 was installed I started to get the “External table is not in the expected format» in my log file.

I ditched the Microsoft Access 2010 Runtime and installed the Microsoft Access 2013 Runtime and my C# code started to work again with no «System.Data.OleDb.OleDbException (0x80004005): External table is not in the expected format.» errors.

2013 version that fixed this error for me
https://www.microsoft.com/en-us/download/confirmation.aspx?id=39358

2010 version that worked for me prior to the update that was automatically installed on my server on December 12th.
https://www.microsoft.com/en-us/download/confirmation.aspx?id=10910
https://www.microsoft.com/en-us/download/confirmation.aspx?id=10910

I also had this error occur last month in an automated process. The C# code ran fine when I ran it debugging. I found that the service account running the code also needed permissions to the C:WindowsTemp folder.

answered Dec 20, 2018 at 20:12

vvvv4dvvvv4d

3,8411 gold badge13 silver badges18 bronze badges

Working with some older code and came across this same generic exception. Very hard to track down the issue, so I thought I’d add here in case it helps someone else.

In my case, there was code elsewhere in the project that was opening a StreamReader on the Excel file before the OleDbConnection tried to Open the file (this was done in a base class).

So basically I just needed to call Close() on the StreamReader object first, then I could open the OleDb Connection successfully. It had nothing to do with the Excel file itself, or with the OleDbConnection string (which is naturally where I was looking at first).

answered Aug 2, 2019 at 19:04

tbonetbone

14.9k3 gold badges33 silver badges40 bronze badges

I’ve had this occur on a IIS website that I host, rarely but periodically this error will popup for files that I’ve previously parsed just fine. Simply restarting the applicable app pool seems to resolve the issue. Not quite sure why though…

answered Feb 4, 2022 at 19:49

David RogersDavid Rogers

2,5664 gold badges39 silver badges82 bronze badges

This happened to us just recently. A customer of ours was getting this error when trying to upload their excel file on our website. I could open the xlsx file fine on ms excel and don’t see any irregularities with the file. I’ve tried all mentioned solutions in here and none worked. And i found this link Treating data as text using Microsoft.ACE.OLEDB.12.0. What worked for our case is adding IMEX=1 attribute to the connection string. So if you are using Microsoft ACE OLEDB 12.0 this might help fix your issue. Hope this helps.

<add name="ExcelTextConnection" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR={1};IMEX=1'" />

answered Apr 14, 2022 at 17:47

sd4ksbsd4ksb

2311 gold badge4 silver badges15 bronze badges

Источник

string sql = "SELECT * FROM [Sheet1$]";
string excelConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathname + ";Extended Properties="Excel 8.0;HDR=YES;IMEX=1;"";

using (OleDbDataAdapter adaptor = new OleDbDataAdapter(sql, excelConnection)) {
    DataSet ds = new DataSet();
    adaptor.Fill(ds);
}

pnuts

57.8k11 gold badges85 silver badges137 bronze badges

asked Jul 16, 2009 at 18:23

«External table is not in the expected format.» typically occurs when trying to use an Excel 2007 file with a connection string that uses: Microsoft.Jet.OLEDB.4.0 and Extended Properties=Excel 8.0

Using the following connection string seems to fix most problems.

public static string path = @"C:srcRedirectApplicationRedirectApplication301s.xlsx";
public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

answered Sep 1, 2009 at 16:38

FAtBalloonFAtBalloon

4,4701 gold badge24 silver badges33 bronze badges

Thanks for this code I really appreciate it. Works for me.

public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

So if you have diff version of Excel file, get the file name, if its extension is .xlsx, use this:

Private Const connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

and if it is .xls, use:

Private Const connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" + path + ";Extended Properties=""Excel 8.0;HDR=YES;"""

answered Feb 11, 2010 at 6:38

TrexTrex

2893 silver badges2 bronze badges

(I have too low reputation to comment, but this is comment on JoshCaba’s entry, using the Ace-engine instead of Jet for Excel 2007)

If you don’t have Ace installed/registered on your machine, you can get it at: https://www.microsoft.com/en-US/download/details.aspx?id=13255

It applies for Excel 2010 as well.

Eunoseer

753 silver badges10 bronze badges

answered Feb 25, 2011 at 14:10

cederlofcederlof

7,0764 gold badges44 silver badges62 bronze badges

answered Jan 22, 2016 at 12:25

I had the same problem. which as resolved using these steps:

1.) Click File

2.) Select «save as»

3.) Click on drop down (Save as type)

4.) Select Excel 97-2003 Workbook

5.) Click on Save button

answered Mar 13, 2019 at 6:49

KamranKamran

6516 silver badges8 bronze badges

I had this same issue(Using the ACE.OLEDB) and what resolved it for me was this link:

http://support.microsoft.com/kb/2459087

C:Program FilesCommon FilesMicrosoft SharedOFFICE14ACEOLEDB.DLL

From the link:

Alternatively, you can modify the registry key changing the dll path to match that of your Access version.

Access 2007 should use OFFICE12, Access 2010 — OFFICE14 and Access
2013 — OFFICE15

(OS: 64bit Office: 64bit) or (OS: 32bit Office: 32bit)

Key: HKCRCLSID{3BE786A0-0366-4F5C-9434-25CF162E475E}InprocServer32

Value Name: (Default)

Value Data: C:Program FilesCommon FilesMicrosoft
SharedOFFICE14ACEOLEDB.DLL

(OS: 64bit Office: 32bit)

Key:
HKCRWow6432NodeCLSID{3BE786A0-0366-4F5C-9434-25CF162E475E}InprocServer32

Value Name: (Default)

Value Data: C:Program Files (x86)Common FilesMicrosoft
SharedOFFICE14ACEOLEDB.DLL

answered Jun 5, 2013 at 12:46

jordanhill123jordanhill123

4,1142 gold badges30 silver badges40 bronze badges

I confirmed this was the issue by:

Making a copy of the file (still had the issue, so it wasn’t some save-as difference)
Selecting all cells in the sheet with the Indirect formulas
Pasting as Values only

and the error disappeared.

answered May 11, 2012 at 19:33

I was getting errors with third party and Oledb reading of a XLSX workbook.
The issue appears to be a hidden worksheet that causes a error. Unhiding the worksheet enabled the workbook to import.

answered Jul 4, 2014 at 18:06

John MJohn M

14k29 gold badges89 silver badges139 bronze badges

If the file is read-only, just remove it and it should work again.

answered Feb 4, 2019 at 22:25

TehscriptTehscript

2,5662 gold badges12 silver badges22 bronze badges

I know this is a very old post, but I can give my contribution too, on how I managed to resolve this issue.

answered Nov 18, 2020 at 14:14

MMJMMJ

5264 silver badges6 bronze badges

I had this problem and changing Extended Properties to HTML Import fixed it as per this post by Marcus Miris:

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & importedFilePathAndName _
         & ";Extended Properties=""HTML Import;HDR=No;IMEX=1"";"

answered Jun 24, 2016 at 13:07

majjammajjam

1,2562 gold badges15 silver badges31 bronze badges

ACE has Superceded JET

Ace Supports all Previous versions of Office

This Code works well!

        OleDbConnection MyConnection;
        DataSet DtSet;
        OleDbDataAdapter MyCommand;
        
        MyConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\Book.xlsx;Extended Properties=Excel 12.0;");
        MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
        DtSet = new System.Data.DataSet();
        
        MyCommand.Fill(DtSet);
        dataGridView1.DataSource = DtSet.Tables[0];
        MyConnection.Close();

answered Oct 3, 2016 at 11:14

Ran into the same issue and found this thread. None of the suggestions above helped except for @Smith’s comment to the accepted answer on Apr 17 ’13.

I suppose ACE OLEDB expects the Excel file to follow a certain XML structure but somehow Reporting Services is not aware of that.

answered Jun 27, 2018 at 8:02

kerweikerwei

1,8021 gold badge13 silver badges22 bronze badges

the file might be locked by another process, you need to copy it then load it as it says in this post

answered Feb 16, 2014 at 23:58

user3140982user3140982

1351 gold badge3 silver badges8 bronze badges

answered Jun 17, 2015 at 9:47

Kieran QuinnKieran Quinn

1,0551 gold badge22 silver badges49 bronze badges

I hope it helps you.

answered Feb 16, 2016 at 6:32

JuanJuan

2,12817 silver badges26 bronze badges

answered Jul 16, 2009 at 18:33

NelsonNelson

4644 silver badges10 bronze badges

answered Feb 2, 2017 at 15:25

KthProgKthProg

2,0001 gold badge23 silver badges32 bronze badges

I recently saw this error in a context that didn’t match any of the previously listed answers. It turned out to be a conflict with AutoVer. Workaround: temporarily disable AutoVer.

answered Apr 26, 2017 at 17:46

unbobunbob

3213 silver badges6 bronze badges

That excel file address may have an incorrect extension. You can change the extension from xls to xlsx or vice versa and try again.

answered Jun 27, 2018 at 8:25

MiMFaMiMFa

8449 silver badges13 bronze badges

2013 version that fixed this error for me
https://www.microsoft.com/en-us/download/confirmation.aspx?id=39358

answered Dec 20, 2018 at 20:12

vvvv4dvvvv4d

3,8411 gold badge13 silver badges18 bronze badges

Working with some older code and came across this same generic exception. Very hard to track down the issue, so I thought I’d add here in case it helps someone else.

In my case, there was code elsewhere in the project that was opening a StreamReader on the Excel file before the OleDbConnection tried to Open the file (this was done in a base class).

answered Aug 2, 2019 at 19:04

tbonetbone

14.9k3 gold badges33 silver badges40 bronze badges

answered Feb 4, 2022 at 19:49

David RogersDavid Rogers

2,5664 gold badges39 silver badges82 bronze badges

<add name="ExcelTextConnection" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR={1};IMEX=1'" />

answered Apr 14, 2022 at 17:47

sd4ksbsd4ksb

2311 gold badge4 silver badges15 bronze badges

Источник

Remove From My Forums

Question
I receive a [DataFormat.Error] External table is not in the expected format error while trying to load my query. My query looks to a folder that contains multiple folders with multiple excel file(300+) all in the same formatting, or supposed
to be. It does not tell me where the table is located so that I can try to fix the problem. I need help troubleshooting this issue as it will most likely pop up again.

Answers

Hi Davis615.

Sometimes that error occured when Excel file is the result of export from a third-party program (accounting system, for example). These files can have a minor errors in XML schema, and they can be easily read by Excel itself, but Power Query raises an error.
I met this errors permanently with my accounting system exports.

The only way I found to fix it is open in Excel, save and close. After that Power Query has no problem with these files.

You can check what file raises an error if you make a query to folder (Folder.Contents or Folder.Files, filter all xlsx files and then add a custom column with formula
```
Excel.Workbook([Contents])
```
If file has a mentioned error, this formula will return an error for this file (keep rows with errors), and you can find all files with problems.

Maxim Zelensky Excel Inside
- Proposed as answer by
  
  Friday, September 8, 2017 11:38 PM
- Marked as answer by
  Imke FeldmannMVP
  Sunday, September 17, 2017 6:42 AM

Источник

public async Task UploadEmployeesAsync(string pathToFile)
{
    string excelConnectionString = GetExcelConnectionString(pathToFile); // Метод из BaseDA
 
    DataTable excelDataTable = new DataTable();
 
    excelDataTable.Columns.AddRange(new DataColumn[12]
    {
        new DataColumn("PersonalNumber",typeof(string)),
        new DataColumn("LastName",typeof(string)),
        new DataColumn("FirstName",typeof(string)),
        new DataColumn("SecondName",typeof(string)),
        new DataColumn("DateOfBirth",typeof(DateTime)),
        new DataColumn("PhoneNumber",typeof(string)),
        new DataColumn("EMail",typeof(string)),
        new DataColumn("EmploymentDate",typeof(DateTime)),
        new DataColumn("DismissalDate",typeof(DateTime)),
        new DataColumn("Foreigner",typeof(int)),
        new DataColumn("Country",typeof(string)),
        new DataColumn("Position",typeof(string))
    });
 
    string excelQuery =
        "SELECT PersonalNumber, LastName, FirstName, SecondName, DateOfBirth, PhoneNumber, EMail, " +
        "EmploymentDate, DismissalDate, Foreigner, Country, [Position] " +
        "FROM [tblHRDEmployee$]";
 
    try
    {
        using (OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(excelQuery, excelConnectionString))
        {
            oleDbDataAdapter.Fill(excelDataTable);
        }
 
        using (SqlConnection sqlConnection = new SqlConnection(_connectionString))
        {
            await sqlConnection.OpenAsync();
 
            using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlConnection,
                       SqlBulkCopyOptions.KeepNulls | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.CheckConstraints,
                       sqlTransaction))
                {
                    sqlBulkCopy.DestinationTableName = "dbo.tblHRDEmployee";
 
                    sqlBulkCopy.ColumnMappings.Add("PersonalNumber", "PersonalNumber");
                    sqlBulkCopy.ColumnMappings.Add("LastName", "LastName");
                    sqlBulkCopy.ColumnMappings.Add("FirstName", "FirstName");
                    sqlBulkCopy.ColumnMappings.Add("SecondName", "SecondName");
                    sqlBulkCopy.ColumnMappings.Add("DateOfBirth", "DateOfBirth");
                    sqlBulkCopy.ColumnMappings.Add("PhoneNumber", "PhoneNumber");
                    sqlBulkCopy.ColumnMappings.Add("EMail", "EMail");
                    sqlBulkCopy.ColumnMappings.Add("EmploymentDate", "EmploymentDate");
                    sqlBulkCopy.ColumnMappings.Add("DismissalDate", "DismissalDate");
                    sqlBulkCopy.ColumnMappings.Add("Foreigner", "Foreigner");
                    sqlBulkCopy.ColumnMappings.Add("Country", "Country");
                    sqlBulkCopy.ColumnMappings.Add("Position", "Position");
 
                    try
                    {
                        await sqlBulkCopy.WriteToServerAsync(excelDataTable);
                        sqlTransaction.Commit();
                    }
                    catch (Exception tranEx)
                    {
                        sqlTransaction.Rollback();
                        throw new ApplicationException("Ошибка транзакции вставки данных", tranEx);
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        throw new ApplicationException("Ошибка загрузки данных", ex);
    }
}

Источник

Исправьте dataformat.errors в Power Bi с помощью этих шагов

При попытке внести изменения в базу данных службы Power BI, например, добавив дополнительные таблицы в базу данных SQL, пользователи Power BI могут столкнуться с различными ошибками формата данных. Некоторые из ошибок включают DataFormat.Error: Мы достигли конца буфера или power bi dataformat.error внешней таблицы не в ожидаемом формате .

Если вы также обеспокоены этими ошибками Power BI, вот несколько советов по устранению неполадок, чтобы решить проблему с несколькими ошибками Dataformat.er.

Как исправить типичные ошибки dataformat.er в Power BI

1. DataFormat.Error: мы достигли конца буфера

Проверьте размер файла

Если ошибка возникает при попытке импортировать данные из нескольких файлов одновременно, это может быть связано с проблемами с размером файла.
Проверьте размер файла JSON, чтобы убедиться, что он не связан с размером вашего файла.

Подожди, подожди и подожди!

Если это временная проблема, то нет смысла пытаться устранить проблему вне вашей зоны комфорта.
Пользователи сообщают, что ошибка формата данных была устранена автоматически через день или два.
Итак, обратитесь в службу поддержки Power BI, если проблема подходит к концу.

Если проблема не устранена, выполните следующие действия.

Если вы делаете PowerQuery, попробуйте отказаться от него и настроить промежуточную таблицу в базе данных SQL, которая анализирует JSON с помощью T-SQL.

Подробнее о добавлении фильтров в Power BI читайте в этом руководстве.

2. Power BI dataformat.error внешняя таблица не в ожидаемом формате

Сохраните файл в Excel

Если вы пытаетесь использовать файл Excel, импортированный из стороннего программного обеспечения, такого как бухгалтерское программное обеспечение, то в нем могут быть незначительные ошибки схемы XML.
Хотя эти ошибки могут игнорироваться приложением Excel, но это приводит к ошибке при использовании с Power Query.
Одним из способов решения этой проблемы является открытие проблемного файла Excel в приложении Excel и его повторное сохранение.
Теперь импортируйте тот же файл в Power Query и проверьте, не возникает ли ошибка снова.
Это может занять много времени, если у вас есть много файлов для работы. Однако, в качестве обходного пути, вы можете решить проблему, пока не будет найдено надежное исправление.

Изменить тип в прикладных шагах

Сначала импортируйте файл, который вы хотите использовать.
В окне навигатора нажмите кнопку « Изменить» рядом с кнопкой « Загрузить» .
На правой панели в разделе « Прикладные шаги» удалите шаг « Изменить тип» .
Нажмите Закрыть и Применить.

Если проблема не устранена, попробуйте удалить начальный измененный тип данных для даты из числа в текст.

3. Power BI dataformat.error неверное значение ячейки # имя / #ref

Попробуйте исправить ошибку в приложении Excel, прежде чем импортировать ее.
Проверьте, имеет ли какая-либо из формул Excel значение # N / A, которое является основной причиной этой проблемы. Замените значение пустым или пробелом.

СВЯЗАННЫЕ ИСТОРИИ, КОТОРЫЕ ВЫ МОЖЕТЕ КАК:

Как добавить запятые к номерам в Power BI [БЫСТРОЕ РУКОВОДСТВО]
Как обновить данные в Power BI [ШАГ ПО ШАГУ]

Источник

Если проблема не устранена, выполните следующие действия.

Если проблема не устранена, попробуйте удалить начальный измененный тип данных для даты из числа в текст.

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Hello. I’m having trouble in PowerQuery with the above error, and I don’t see anything in this forum that matches my problem exactly.

I have tried this with different files, different directories, I have copied the query file, I have rebuilt the query file from scratch and nothing seems to help. The problem does not seem to happen when I first write the query, but when I exit out and back in I consistently get the error.

I have pasted the entire query below (none of which is confidential).

Any ideas on this would be greatly appreciated.

Видео: How to use Microsoft Power BI 2023

При попытке внести изменения в базу данных службы Power BI, например, добавив дополнительные таблицы в базу данных SQL, пользователи Power BI могут столкнуться с различными ошибками формата данных. Некоторые ошибки включают DataFormat.Error: Мы достигли конца буфера или power bi dataformat.error внешней таблицы не в ожидаемом формате.

Как исправить типичные ошибки dataformat.er в Power BI

1. DataFormat.Error: мы достигли конца буфера

Проверьте размер файла

Если ошибка возникает при попытке импортировать данные из нескольких файлов одновременно, это может быть связано с проблемами с размером файла.
Проверьте размер файла JSON, чтобы убедиться, что он не связан с размером вашего файла.

Подожди, подожди и подожди!

Если это временная проблема, то нет смысла пытаться устранить проблему вне вашей зоны комфорта.
Пользователи сообщают, что ошибка формата данных была устранена автоматически через день или два.
Итак, обратитесь в службу поддержки Power BI, если проблема подходит к концу.

Если проблема не устранена, выполните следующие действия.

Если вы делаете PowerQuery, попробуйте отказаться от него и настроить промежуточную таблицу в базе данных SQL, которая анализирует JSON с помощью T-SQL.

3. Power BI dataformat.error неверное значение ячейки # имя / #ref

Попробуйте исправить ошибку в приложении Excel, прежде чем импортировать ее.
Проверьте, имеет ли какая-либо из формул Excel значение # N / A, которое является основной причиной этой проблемы. Замените значение пустым или пробелом.

Как устранить проблемы с нехваткой места на диске в Windows 10

Если вы получаете уведомления о нехватке места на диске E, отключите защиту системы, щелкнув правой кнопкой мыши «Пуск» и выбрав «Система».

Как устранить проблемы с батареей Surface Pro 3: избавиться от устройства

Поверхность Pro 3 печально известна своими проблемами разрядки батареи. В течение нескольких месяцев Microsoft пыталась решить эти проблемы с батареями, но безрезультатно. Устройства Surface Pro 3, работающие от аккумуляторов LGC, особенно подвержены этой ошибке, и, похоже, это будет продолжаться в течение длительного времени. Microsoft недавно выкатил .

Как устранить ошибку привязки power bi?

Если вы получили ошибку привязки Power BI, исправьте ее сейчас, отладив, переустановив сертификаты SSL, обновив NodeJS или избежав определенных переменных.

Источник

Power query dataformat error внешняя таблица не имеет предполагаемый формат

Есть два файла.
При настройке запроса Power Query на эти файлы «Из папки» запрос не формируется.

DataFormat.Error: Входные данные не удалось распознать как допустимый документ Excel.
Подробные сведения:
Binary

По отдельности запрос на файлики формируется нормально.
Файлы не могу приложить из-за их размера.
В чем может быть проблема?

Есть два файла.
При настройке запроса Power Query на эти файлы «Из папки» запрос не формируется.

DataFormat.Error: Входные данные не удалось распознать как допустимый документ Excel.
Подробные сведения:
Binary

По отдельности запрос на файлики формируется нормально.
Файлы не могу приложить из-за их размера.
В чем может быть проблема? Viper25

Сообщение Есть два файла.
При настройке запроса Power Query на эти файлы «Из папки» запрос не формируется.

DataFormat.Error: Входные данные не удалось распознать как допустимый документ Excel.
Подробные сведения:
Binary

По отдельности запрос на файлики формируется нормально.
Файлы не могу приложить из-за их размера.
В чем может быть проблема? Автор — Viper25
Дата добавления — 19.09.2016 в 17:51

Szekerfehesvar

Дата: Понедельник, 19.09.2016, 21:23 | Сообщение № 2

krosav4ig

Дата: Понедельник, 19.09.2016, 23:45 | Сообщение № 3

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Viper25

Дата: Вторник, 20.09.2016, 12:42 | Сообщение № 4

Viper25

Дата: Вторник, 20.09.2016, 15:28 | Сообщение № 5

krosav4ig

Дата: Четверг, 22.09.2016, 03:43 | Сообщение № 6

«)
),
Книги = Table.CombineColumns(
Table.SelectColumns(
Table.AddColumn(
Файлы,
«Workbook»,
each
Table.SelectRows(
Excel.Workbook([Content]),
each
_[Kind] = «Sheet»
)
),
<«Name», «Folder Path», «Workbook»>
),
<«Folder Path», «Name»>,
Combiner.CombineTextByDelimiter(
«»,
QuoteStyle.None
),
«Файл»
),
Листы = Table.TransformColumns(
Table.ExpandTableColumn(
Книги,
«Workbook»,
<«Name», «Data»>,
<«Лист»,»Data»>
),
<«Data», Table.PromoteHeaders>
),
Столбцы = Table.Distinct(
Table.ExpandListColumn(
Table.AddColumn(
Листы,
«»,
each
Table.ColumnNames([Data])
),
«»
),
<«»>
)[#»»],
Таблица = Table.ExpandTableColumn(
Листы,
«Data»,
Столбцы
)
in
Таблица

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Viper25

Дата: Четверг, 22.09.2016, 09:55 | Сообщение № 7

krosav4ig

Дата: Четверг, 22.09.2016, 14:37 | Сообщение № 8

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Viper25

Дата: Четверг, 22.09.2016, 14:44 | Сообщение № 9

Viper25

Дата: Четверг, 22.09.2016, 14:57 | Сообщение № 10

pupkin-terkin

Дата: Вторник, 27.12.2016, 16:41 | Сообщение № 11

Вот тут ещё пример (выходящий за рамки вопроса) с картинками:
http://www.poweredsolutions.co/2014. y-way-2

Вот тут ещё пример (выходящий за рамки вопроса) с картинками:
http://www.poweredsolutions.co/2014. y-way-2 Автор — pupkin-terkin
Дата добавления — 27.12.2016 в 16:41

telnoff

Дата: Вторник, 12.11.2019, 14:27 | Сообщение № 12

. И ввиду такого названия он автоматически становился первым в списке. Т.е. тебе нужно отфильтровать таблицу с перечнем файлов до того как эти файлы начнут считываться. Делается это в расширенном редакторе.
В Power Bi есть кое какая хитрая система вспомогательных запросов, ты о них не знаешь, но они создаются автоматически и в ходе работы ссылаются на них, так вот этот автоматический запрос также придется редактировать и ставить дополнительный фильтр на исключение временных файлов.

Источник

Adblock
detector

Источник

Hi all,

I have been working with Power Query as a way to combine datasets from two systems then plugging it into PowerBI for about a year now. This month, when I ran and refreshed data for 2018 year-end, I am getting an error I have never gotten before:

There are always a few things to tweak here or there like adjusting the files that have been added to the source (SharePoint) so that it narrows down to the correct file. That is not the problem here. It seems to only error out at the step where the table is expanded, before any of the transformation steps. I have gone through my source files and checked their columns for data type and they’re all normal. A few of the tables weren’t updated at all, however, I understand that if one file errors out — all subsequent files will error the same way. Is there a way to find out which file is causing it? I’m not sure where in the grand scheme of things I should look for file order. Any insight would be immensely helpful!

Note: the very first file and query are not getting an error. However, there are a jumble of non-linear queries and files from there on out (i.e., file #2 in SharePoint doesn’t match query #2 in Power Query — which one matters here?)

Источник

Hi all,

Источник

ACE has Superceded JET

ACE has Superceded JET

Question

Answers

Как исправить типичные ошибки dataformat.er в Power BI

1. DataFormat.Error: мы достигли конца буфера

2. Power BI dataformat.error внешняя таблица не в ожидаемом формате

3. Power BI dataformat.error неверное значение ячейки # имя / #ref

Как исправить типичные ошибки dataformat.er в Power BI

1. DataFormat.Error: мы достигли конца буфера

3. Power BI dataformat.error неверное значение ячейки # имя / #ref

Как устранить проблемы с нехваткой места на диске в Windows 10

Как устранить проблемы с батареей Surface Pro 3: избавиться от устройства

Как устранить ошибку привязки power bi?

Power query dataformat error внешняя таблица не имеет предполагаемый формат