1
00:00:00,080 --> 00:00:05,584
This fictional order file has three rows and one hundred fifty-nine dollars in sales.

2
00:00:05,584 --> 00:00:09,620
A quick CSV open may turn ZIP zero two one zero

3
00:00:09,620 --> 00:00:11,822
eight into two one zero eight.

4
00:00:11,822 --> 00:00:14,757
It may also alter a seventeen-digit reference.

5
00:00:14,757 --> 00:00:18,794
We will keep those identifiers as text while leaving the dollar

6
00:00:18,794 --> 00:00:22,096
amounts numeric, then compare against an exact answer sheet.

7
00:00:22,696 --> 00:00:27,397
Download orders-ids-practice dot C S V and look at it in

8
00:00:27,397 --> 00:00:29,206
a plain text editor first.

9
00:00:29,206 --> 00:00:33,184
Order O two oh one contains customer code zero zero zero

10
00:00:33,184 --> 00:00:37,162
one two three, ZIP zero two one zero eight, and a

11
00:00:37,162 --> 00:00:38,970
reference ending five six seven.

12
00:00:38,970 --> 00:00:42,948
The other references end five six eight and five six nine.

13
00:00:42,948 --> 00:00:45,480
These are identifiers, not quantities to calculate.

14
00:00:46,080 --> 00:00:51,679
Excel uses its default conversion rules when a C S V is opened directly.

15
00:00:51,679 --> 00:00:56,078
A leading zero can disappear because the code becomes a number.

16
00:00:56,078 --> 00:00:59,677
Numbers longer than fifteen significant digits can lose precision.

17
00:00:59,677 --> 00:01:02,877
Changing cell display formatting afterward cannot reconstruct digits

18
00:01:02,877 --> 00:01:05,676
that have already been removed or rounded.

19
00:01:05,676 --> 00:01:10,475
Go back to the untouched source file before trying the controlled import.

20
00:01:11,075 --> 00:01:15,217
In Excel desktop, choose Data, Get Data, From File, then From

21
00:01:15,217 --> 00:01:17,100
Text slash C S V.

22
00:01:17,100 --> 00:01:21,995
Some ribbons offer a shorter Data, From Text slash C S V command.

23
00:01:21,995 --> 00:01:26,137
Select the practice file and check that commas separate five columns.

24
00:01:26,137 --> 00:01:31,032
Choose Transform Data so the query editor opens before rows reach the sheet.

25
00:01:31,032 --> 00:01:33,667
Menu labels vary by release and language.

26
00:01:34,267 --> 00:01:36,616
In Power Query, inspect Applied Steps.

27
00:01:36,616 --> 00:01:40,922
Microsoft documents that type detection can add a Changed Type step

28
00:01:40,922 --> 00:01:42,880
to C S V queries.

29
00:01:42,880 --> 00:01:46,794
If that step already made CustomerCode, ZIP, or Reference numeric,

30
00:01:46,794 --> 00:01:49,926
remove or revise it before setting their types.

31
00:01:49,926 --> 00:01:54,232
Otherwise converting the already rounded result back to Text would only

32
00:01:54,232 --> 00:01:55,798
preserve the damaged value.

33
00:01:55,798 --> 00:01:57,755
Check the raw preview again.

34
00:01:58,355 --> 00:02:02,914
Select CustomerCode, ZIP, and Reference, then choose Text as the data type.

35
00:02:02,914 --> 00:02:07,093
Keep AmountUSD as a decimal number, because we need its sum.

36
00:02:07,093 --> 00:02:10,892
Microsoft's support instructions use Home, Transform, Data Type, Text,

37
00:02:10,892 --> 00:02:14,691
although the exact control may look different in your build.

38
00:02:14,691 --> 00:02:18,869
The three reference endings should still be distinct: five six seven,

39
00:02:18,869 --> 00:02:21,529
five six eight, and five six nine.

40
00:02:22,129 --> 00:02:26,933
Choose Close and Load, then compare your imported result with the answer workbook.

41
00:02:26,933 --> 00:02:30,998
The target is three order rows, a first ZIP of zero

42
00:02:30,998 --> 00:02:36,172
two one zero eight, and different final digits in the first and last references.

43
00:02:36,172 --> 00:02:40,607
Sum the AmountUSD values: forty-eight plus seventy-five plus thirty-six

44
00:02:40,607 --> 00:02:42,824
equals one hundred fifty-nine dollars.

45
00:02:42,824 --> 00:02:47,259
If any code differs, reopen the untouched CSV and revisit type steps.

46
00:02:47,859 --> 00:02:51,839
A correctly configured import should keep every ID exactly as the

47
00:02:51,839 --> 00:02:56,905
source wrote it and still give a numeric one hundred fifty-nine dollar total.

48
00:02:56,905 --> 00:03:00,885
Confirm those values before saving the result as an Excel workbook.

49
00:03:00,885 --> 00:03:05,227
If you later export to C S V, remember that double-clicking

50
00:03:05,227 --> 00:03:08,484
that export can trigger the same automatic conversion again.

51
00:03:08,484 --> 00:03:11,379
The guide links every step and practice file.