Google Analytics xlsx format import issue

Antal_SofalvyAntal_Sofalvy MemberPosts:13Contributor II
edited November 2018 inHelp

Hello

We export data from Google Analytics / Webmaster Tools / AdWords... Export -> Excel (xlsx format)

We tried "Read Excel" Operator on this file, but it gives an error; the Import Config Wizard stucks, too.

Pls find a file attached.

What are we doing wrong?

Thanks,

Antal

PS

It comes from Google Enterprise Account, but I'm afraid normal account files are the same.

Tagged:

Best Answer

  • Marco_BoeckMarco_Boeck Administrator, Moderator, Employee, Member, University ProfessorPosts:1,984RM Engineering
    Solution Accepted

    Hi,

    thanks for the report!

    If you open the .xlsx file with Excel, it will immediately be modified. If you now save it again (without doing anything except having opened it), it will load successfully in Studio. So I guess the format you get from Google does not comply with the ECMA-376, 4th Edition standard:(

    I'm not sure we can circumvent that problem on our side so my advice would be to create a bug report at Google so they actually comply with the standard defintion.

    Regards,

    Marco

    MCs

Answers

  • Antal_SofalvyAntal_Sofalvy MemberPosts:13Contributor II

    Hello Marco

    Thank you for the turnaround, actually this is what we did - on the other hand:

    - we have 1000s of analytics reports (weekly), with automatic updates

    - the size roughly doubles after open/save

    Anyhow thanks for your suggestion!

    Cheers,

    Antal

    MCs
  • Telcontar120Telcontar120 Moderator, RapidMiner Certified Analyst, RapidMiner Certified Expert, MemberPosts:1,635Unicorn

    Perhaps exporting in a different format would relieve the necessity of a workaround? I believe Google Analytics also allows report exports in other simpler formats, such as csv and tsv, both of which are also readable by RapidMiner.

    Regards,

    Brian T.
    Lindon Ventures
    Data Science Consulting from Certified RapidMiner Experts
    MartinLiebig
  • Antal_SofalvyAntal_Sofalvy MemberPosts:13Contributor II

    Hello,

    Good idea, thank you for sharing!

    However in this situation we have to consider other factors:

    - All the data has been generated / saved in xlsx for years

    ——谷歌csv其他问题:例如character coding is changing sometimes "randomly" (utf-16, utf-8, ISO-whatever..., ) that make things little challenging

    - paralel reporting / BI / Pred tools uses these xlsx format files

    - +++

    Originally I wanted to make things easier using xlsx - due to the csv issues we have been encountering for months so far;

    tsv testing is coming up next:)

    Thanks,

    Cheers,

    Antal

Sign InorRegisterto comment.