Forum Discussion
Changes to file before loading
Hi guys!
I'm having a problem on my file. We have mora than 2000 lines of content but, 13 of them, has html code in it. Importing is giving me 13 errors. I'd like to be able make a change on the text before inserting the values in cells. Is that possible?
We're doing this manually, everyday. :-(
Thanks!
8 Replies
Do you get the errors on type convertion?
Try replace the values.
If you still get the error ensure your replace is before the error. You can reorder the steps by clicking and selecting move up or down
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk5JLFaK1YlWSoSzTE2MTcCM4pS0lLR0MNPYzLQ4pSgNzI4pNTAwTk4qAtOpJanFJcUQIX2YGNSYlLTi9AygmbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [data = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"<br>","",Replacer.ReplaceText,{"data"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","</br>","",Replacer.ReplaceText,{"data"}), #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"data", type text}}) in #"Changed Type"If this fails you can also replace errors if you just want to avoid the error.
Yes, because since is has html code, it's importing those 13 fields to the wrong columns. it's like having a line break in the middle of the file 13 times.
let
Source = Folder.Files("D:\"),
#"D:\track csv" = Source{[#"Folder Path"="D:\",Name="tracks.csv"]}[Content],
#"Imported CSV" = Csv.Document(#"D:\tracks csv",[Delimiter=",", Columns=62, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"id", type text}, {"autoSlug", type text}, {"name", type text}, {"tags", type text}, {"status", type text}, {"countAllTimeVideosWatched", type number}, {"countAllTimeVideosWatched50p", type number}, {"countAllTimeEventsPlayerFavoritar", Int64.Type}, {"countAllTimeEventsPlayerMenuDownloadClick", Int64.Type}, {"countAllTimeEventsTelaCompartilhar", Int64.Type}, {"nameMultiline", type text}, {"description", type text}, {"type", type text}, {"notes", type text}, {"state", type text}, {"publishedAt", type datetime}, {"isReady", type logical}, {"isPublished", type logical}, {"submittedAt", type datetime}, {"typedWho", type text}, {"typedWhere", type text}, {"typedSport", type text}, {"typedManeuver", type text}, {"typedProducer", type text}, {"typedElement", type text}, {"typedWhat", type text}, {"typedSoundtrack", type text}, {"typedMission", type text}, {"typedMood", type text}, {"typedFlag", type text}, {"permanentSlug", type text}, {"relativeURL", type text}, {"absoluteURL", type text}, {"shortlink", type text}, {"shortlinkId", type text}, {"shortlinkAbsoluteUrl", type text}, {"metadataTitle", type text}, {"metadataDescription", type text}, {"positionBoost", Int64.Type}, {"cachedDefaultPlaylistRelativeURL", type text}, {"cachedDefaultPlaylistAbsoluteURL", type text}, {"cachedDurationMs", type number}, {"cachedTransformationURLHls", type text}, {"cachedTransformationURLMpegDash", type text}, {"cachedTransformationURL360p", type text}, {"cachedTransformationURL480p", type text}, {"cachedTransformationURL720p", type text}, {"cachedTransformationURL1080p", type text}, {"cachedTransformationURLStories", type text}, {"analyserLastCheckedAt", type datetime}, {"analyserCheckedCount", type number}, {"createdAt", type datetime}, {"updatedAt", type datetime}, {"isDeleted", type logical}, {"deletedAt", type text}, {"toBeDeleted", type logical}, {"createdBy", type text}, {"createdById", type text}, {"submittedBy", type text}, {"submittedById", type text}, {"submission", type text}, {"submissionId", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","Novidades OFF, ","",Replacer.ReplaceText,{"typedWhat"})
in
#"Replaced Value"Try Deleting the step "Changed Type" and remove any problem data.
Then you can the type convertion back using Transform - Detect Data Types or by setting each column.
If this doesn't work provide me a few rows (including a bad row) from the tracks file.
