How can we change the source from excel work book to CSV

[Sohaib]

Hi,

     I was thinking about my code which took data from folder in Excel work book format. But problem I realised now that Xlsx format is very heavy as compare to csv. Is there any approach in which i change from xlsx to csv without breaking my code. Other thing is that in future i need to add new columns in csv so i need the code which is dynamic to read new column in csv.

let
Source = Folder.Files("C:\Users\dell\Documents\Working\Data Validation\KPI Calculations 28-2-2024\KPI Calculation new method working\data 5.2"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Date", type text}, {"Id", type text}, {"measObjLdn", type text}, {"MsgTag", type text}, {"EndTime", type datetime}, {"BeginTime", type datetime}, {"File", type text}, {"pmCellDowntimeAuto", Int64.Type}, {"pmPdcpVolDlDrb", Int64.Type}, {"pmSchedActivityCellDl", Int64.Type}, {"pmPdcpVolDlDrbLastTTI", Int64.Type}, {"pmUeThpTimeDl", Int64.Type}, {"pmPdcpVolDlDrbQci", type text}, {"pmPdcpVolDlDrbLastTTIQci", type text}, {"pmDrbThpTimeDlQci", type text}, {"pmPdcpVolDlSrb", Int64.Type}, {"pmErabEstabAttInit", Int64.Type}, {"pmErabEstabAttAdded", Int64.Type}, {"pmErabRelAbnormalEnbAct", Int64.Type}, {"pmErabRelAbnormalEnb", Int64.Type}, {"pmErabRelNormalEnb", Int64.Type}, {"pmErabRelMme", Int64.Type}, {"pmRrcConnEstabSucc", Int64.Type}, {"pmRrcConnEstabAtt", Int64.Type}, {"pmRrcConnEstabAttReatt", Int64.Type}, {"pmS1SigConnEstabSucc", Int64.Type}, {"pmS1SigConnEstabAtt", Int64.Type}, {"pmErabEstabSuccInit", Int64.Type}, {"pmErabEstabSuccAdded", Int64.Type}, {"pmRrcConnLevSum", Int64.Type}, {"pmPdcpVolUlDrb", Int64.Type}, {"pmSchedActivityCellUl", Int64.Type}, {"pmPrbUtilUl", type text}, {"pmUeThpVolUl", Int64.Type}, {"pmUeThpTimeUl", Int64.Type}, {"pmRadioThpVolDl", Int64.Type}, {"pmPrbUsedDlDtch", Int64.Type}, {"pmRadioUeRepCqiDistr", type text}, {"pmPdcpVolUlSrb", Int64.Type}, {"pmSessionTimeDrbQci", type text}, {"pmErabRelAbnormalEnbActQci", type text}, {"pmErabRelAbnormalMmeActQci", type text}, {"pmErabRelAbnormalEnbQci", type text}, {"pmErabRelNormalEnbQci", type text}, {"pmErabRelMmeQci", type text}, {"pmErabEstabSuccAddedQci", type text}, {"pmErabEstabSuccInitQci", type text}, {"pmErabEstabAttInitQci", type text}, {"pmErabEstabAttAddedQci", type text}, {"pmErabEstabAttAddedHoOngoingQci", type text}, {"pmVoipQualityRbUlOk", Int64.Type}, {"pmVoipQualityRbUlNok", Int64.Type}, {"pmVoipQualityUeUlOk", Int64.Type}, {"pmVoipQualityUeUlNok", Int64.Type}, {"pmErabQciLevSum", type text}, {"pmPrbUtilDl", type text}, {"eNodeB", type text}, {"uarfcnDl", type any}, {"UtranCell", type any}, {"NodeB", type any}, {"Sector", type any}, {"earfcndl", type any}, {"Frequency", type any}, {"CellID", type any}, {"eNBId", type any}, {"RAT", type any}, {"SiteName", type any}, {"eUtranCell", type text}, {"Version", type text}}),
#"Inserted Date" = Table.AddColumn(#"Changed Type", "Date.1", each DateTime.Date([BeginTime]), type date),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Date",{{"Date.1", "Date New"}}),
#"Inserted Text After Delimiter" = Table.AddColumn(#"Renamed Columns", "Text After Delimiter", each Text.AfterDelimiter([measObjLdn], "EUtranCellFDD="), type text),
#"Reordered Columns" = Table.ReorderColumns(#"Inserted Text After Delimiter",{"Source.Name", "Date", "Id", "measObjLdn", "MsgTag", "Text After Delimiter", "EndTime", "BeginTime", "File", "pmCellDowntimeAuto", "pmPdcpVolDlDrb", "pmSchedActivityCellDl", "pmPdcpVolDlDrbLastTTI", "pmUeThpTimeDl", "pmPdcpVolDlDrbQci", "pmPdcpVolDlDrbLastTTIQci", "pmDrbThpTimeDlQci", "pmPdcpVolDlSrb", "pmErabEstabAttInit", "pmErabEstabAttAdded", "pmErabRelAbnormalEnbAct", "pmErabRelAbnormalEnb", "pmErabRelNormalEnb", "pmErabRelMme", "pmRrcConnEstabSucc", "pmRrcConnEstabAtt", "pmRrcConnEstabAttReatt", "pmS1SigConnEstabSucc", "pmS1SigConnEstabAtt", "pmErabEstabSuccInit", "pmErabEstabSuccAdded", "pmRrcConnLevSum", "pmPdcpVolUlDrb", "pmSchedActivityCellUl", "pmPrbUtilUl", "pmUeThpVolUl", "pmUeThpTimeUl", "pmRadioThpVolDl", "pmPrbUsedDlDtch", "pmRadioUeRepCqiDistr", "pmPdcpVolUlSrb", "pmSessionTimeDrbQci", "pmErabRelAbnormalEnbActQci", "pmErabRelAbnormalMmeActQci", "pmErabRelAbnormalEnbQci", "pmErabRelNormalEnbQci", "pmErabRelMmeQci", "pmErabEstabSuccAddedQci", "pmErabEstabSuccInitQci", "pmErabEstabAttInitQci", "pmErabEstabAttAddedQci", "pmErabEstabAttAddedHoOngoingQci", "pmVoipQualityRbUlOk", "pmVoipQualityRbUlNok", "pmVoipQualityUeUlOk", "pmVoipQualityUeUlNok", "pmErabQciLevSum", "pmPrbUtilDl", "eNodeB", "uarfcnDl", "UtranCell", "NodeB", "Sector", "earfcndl", "Frequency", "CellID", "eNBId", "RAT", "SiteName", "eUtranCell", "Version", "Date New"}),

code is more than 20,000 character so i can not add complete code here. I have attached the code.

download Code  

Help requires.

[v-xinruzhu-msft]

Hi @Sohaib 

You can create a blank query, then connect to the csv file first, then in original code,replace the code with the code in blank query, that you connect to the csv file. and you can refer to the following link.

Solved: Change from xlsx to csv - Microsoft Fabric Community

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.